Assigning a primary key after import using VBA

[ReportSmith]

This is probably simple, but once again, I'm stuck.

I have an Excel file that I have imported into an Access table (via VBA). I
now would like to make one of the imported fields a primary key in the new
table (again, using VBA). I know how to do it via the import wizard ("Choose
my own primary key.")

Once the variables were declared/defined, I used the following:
DoCmd.TransferSpreadsheet acImport, 8, "Original", ClientFileFolder &
ClientFile, True, ""

....to import into a new table, called "Original". I now need to make one of
the fields a PK.

Any help is greatly appreciated.

 

[John W. Vinson]

This is probably simple, but once again, I'm stuck.

I have an Excel file that I have imported into an Access table (via VBA). I
now would like to make one of the imported fields a primary key in the new
table (again, using VBA). I know how to do it via the import wizard ("Choose
my own primary key.")

Once the variables were declared/defined, I used the following:
DoCmd.TransferSpreadsheet acImport, 8, "Original", ClientFileFolder &
ClientFile, True, ""

...to import into a new table, called "Original". I now need to make one of
the fields a PK.

Any help is greatly appreciated.

If the structure of the Excel file is predictable, I'd suggest creating an
empty table FIRST, defining a primary key as appropriate; and importing into
the table, rather than importing first and patching up later.

How will you deal with the possibility that the spreadsheet has data (null or
duplicate values) in the putative primary key field?

John W. Vinson [MVP]

 

[ReportSmith]

John,
Thanks for the reply. I figured it out. You have a good point about the
primary key field. The file is actually a processed text file that comes
from a separate (Oracle?) db. There is a "ID" field with unique autonumber
values.

What I did was import the text file using "DoCmd.TransferText" and a import
spec I set up earlier. Once the data was in Access, I tried to do a "ALTER
TABLE" SQL statement in VBA, but couldn't get the syntax to work. So, I
created a Data Definition Query (New query>>Query Menu>>SQL Specific
submenu>>SQL Specific option):

ALTER TABLE Results ADD CONSTRAINT xID PRIMARY KEY (ID)

and named it "qrySetPK". Then, I used the following statement:

DoCmd.OpenQuery "qrySetPK"

and it did the trick. The biggest issue I (almost always) come across is
the syntax. Once I understood what the statement needed to include (and the
correct way of writing it), I got it to work.

The articles where I got the info are:
http://support.microsoft.com/kb/q209037 and
http://support.microsoft.com/kb/291539/EN-US/
(I'm using Access 2002, but there are also links for 2000 and 97).

Thanks again.