TransferText macro - need Access to create index

[Guest]

I am running Access 2003. Per Steve Schapel's suggestion, I am trying to
create a macro so users can repetitively import data into a database.

I can't figure out how to get Access to created an indexed field when it
runs the macro. When I do a manual File > Get external data, I can select to
have Access create an index. I can't seem to do this in the automated
TransferText method. I've looked in the help and read the TransferText posts
here but I'm just not seeing what I'm doing wrong. I've gone through the
File > Get external data process several times to save the import
specifications. I'm saving the specifications at the very last step of the
import process.

As always, thanks for any help you can give me.

Ann Scharpf

 

[Ken Snell \(MVP\)]

By "index", do you mean a primary key with unique values? If yes, why not
just import into an existing table that has an autonumber field as the
"last" field (so that the text file will not try to use it). You can delete
all records from the table before you import the next text file.

 

[Guest]

Aha! I didn't realize I could have the key field be the LAST field in the
table. And that, by putting a field at the end, the import wouldn't try to
use it. I kept getting an error that said "Microsoft Office Access was
unable to append all the data to the table." because I had the Autonumber ID
field at the beginning of the record when I tried this before.

Thanks! I never would've thought of trying this.

Ann