Help creating primary key field automatically

[ecoli]

Hi all,

I am importing a text table into Access. I created an import specification and I am using a macro to automatically import this text into a table at various times throughout the day. I have a problem though, in that my text table does not have a field that I can designate as the primary key. I would like to be able to have my macro create an autonumber field and designate it as the primary key after it imports the table. Unfortunately, I haven't been able to figure out how to do this without human intervention. I want this to part of the macro, no input from me required.

If someone can help me with this, or if you have a better idea, I'd sure appreciate your input.

Thanks,
Eric

 

[montrose]

Eric, Do you have a primary key identified in the table to which you are already importing the data? Where is the text data coming from? How is it related to the info in the table? What is unique about the text data? May be able to help if there was more info. Thanks, Montrose Learn what you can and share what you know.

 

[ecoli]

Hi Montrose,

Thanks for the reply. My data file is a text dump from an AS400, saved as a fixed-width text file. There is not an index in this file,and there is not any one field that has unique values, but you could make an index from the combination of two fields. I'd like to have a primary key to speed things up.

I created and saved an import specification which allows me to define the field widths and types. It also allows me to index the fields. The macro I wrote first deletes the existing table in the database, then imports the text file using the file import specification into the table. However, I haven't been able to figure out how to specify a primary key using the import specification, or to how to make a primary key automatically after I import the table.

The goal of all this is to automatically create an indexed table with a primary key, from a table that does not have a primary key. I was hoping to do it via a macro or module. I want this to be able to run with no human intervention.

Hopefully I've given you the information you need. Once again, thanks for your quick reply!

Thanks,
Eric

 

[WildHare]

Isn't there a step somewhere in the import spec building process that says "add a primary key to my table" ??? I haven't used Import Specs for a while, maybe I'm getting senile.

Your other option is to NOT delete the target table, just select all the records and delete THEM. If your target table has a key field with autonumber as the type, each record will be given a sequential number as it slips by.

78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ecoli]

Hi WildHare,
You are correct, there is a step to add a primary key when you go through the process manually. However, I can't find a way to make the import specification do it automatically.

Regarding the deleting the records only, that may work, but I'd be worried about running up astromical numbers in a hurry in the autonumber field. My table has around 200,000 records or so. If I remember right, when you delete the records, autonumber still remembers where it was at and keeps going instead or resetting. If I import my table once an hour, it wouldn't take long to get some huge numbers. Of course, I might not be remembering correctly.

Hope this clarifies things, thanks for replying!

Eric

 

[WildHare]

You're importing 200K records per hour?...hoo boy.. You're right about autonumber not resetting.

You're going to have to proceduralize this process, it just won't be doable with a macro. I believe you can use VBA to

1) Delete the table (if out there..)
2) Create a tableDef with a primay key and your other fields.
3) Import the existing table to a temp table.
4) APPEND the temp records to the operational guy you created with a tabledef.
5) Delete the temp guy
6) After an hour, go back to step 1...

I Have NOT ever done this, but I've seen code that does, so I'm pretty sure it's within the realm of possibility.







78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm