Creating a primary key on imported table

[Morris]

Hi again!

I've imported the text file to a table "customer list"

Now I want the first column in the table ('Field1') to be a primary
key. I tried to save it as a specification, that I can re-use when
doing the import but I think it's not saved there, as it didn't work.

So my question - I've got 4 different tables in Access. I'm importing
text file to a fifth table. And I want to make the 'Field1' a primary
key using VBA code. How can I achieve that?

Thanks

 

[kingston via AccessMonster.com]

Although not quite what you're asking, you can achieve the same goal via the
following:
Create a link to the data source.
Create a table with the desired structure (with a primary key).
Create an append query to import the data from the link into your properly
structured table.
If needed, you can create a delete query to clear the table for import next
time.
Code this process in VBA if you want.

 

[RoyVidar]

Hi again!

I've imported the text file to a table "customer list"

Now I want the first column in the table ('Field1') to be a primary
key. I tried to save it as a specification, that I can re-use when
doing the import but I think it's not saved there, as it didn't work.

So my question - I've got 4 different tables in Access. I'm importing
text file to a fifth table. And I want to make the 'Field1' a primary
key using VBA code. How can I achieve that?

Thanks

I don't know about the the import spec, but I suspect you are
correct, that you can define unique or non-unique index, but not
primary key.

Often, one is recommended to use DAO to alter such properties, but
here's a smallish air code DDL

dim strSql as string
strSql = "ALTER TABLE [customer list] " & _
"ADD Constraint PK_CustomerList PRIMARY KEY (Field1)"
CurrentDB.Execute strSql, dbFailOnError

I don't use those import methods much, but aren't there a possibility
of importing to an existing table? If so, you could create it with
the structure you need, then just delete the existing records before
importing again.