Need AutoNum in table

[Guest]

Hi there. Using A02 on XP. Have created import specs and when I use the
Wizard and import with or without the specs, I get an AutoNum field
automatically added to the table. BUT, when I create a macro to TransferText
and click the command button to run the macro and import using the specs, the
table does NOT have an AutoNum field. I really need an AutoNum field. How
can I add an AutoNum field to my specs? Tried to create the field in my
specs but it won't 'stick'. (Data is being imported into a new table not an
existing one.)

I'd appreciate any help or advice on this. Trying to reduce need for user
intervention in manipulating files and data.

Thanks in advance for your time!

 

[Ken Snell \(MVP\)]

Why not create the table that you can import the data into, and then add one
extra field as the "last" field -- an autonumber field. Then you can delete
all records from the table to empty it, do a new import to the table, etc.
An autonumber field does not have to be the "first" field.

 

[Guest]

Thank you Ken for your advice. My goal is this: import a text file
(GP0001.txt), spit out an Excel file with SSN's removed and containing a
unique ID (autonum field). When the client returns the file with edits and
additions, we run a compare and where the ID matches, export the SSN's out to
the edited Excel file.

I want to import each contract's data into separate tables because I do not
have the contract number in the data, only in the file name. How could I
import the data AND 'pad' a new field with the contract number (GP0001)? If
I could do that then I could see having just one table. (I can have them
open a form with a list box showing all the contracts and use the value of
the field to pad it. Just don't know how to use it on the append.)

Sorry, I'm not a programmer so I'm having to go at this slowly. Enjoying
the learning process. Can do macros and learning VB.

Thanks again and in advance for any further help or advice on this.

P.S. Is is better to import in code or using a macro?

 

[Ken Snell \(MVP\)]

If you do a "simple" import using a macro (TransferSpreadsheet) or VBA
(DoCmd.TransferSpreadsheet), then you cannot put the file name into a field
name as part of the import unless the file name is in a cell on each row of
the spreadsheet.

If you want to "identify" each record when you import it using the file
name, you'll need to use VBA code to open the EXCEL file, and then read one
row at a time and write the data to the table, and add the filename as one
of the data items for each record. Probably a bit of overkill for what you
want to do here, though.

So using separate tables is probably your best bet here. As for which is
better -- macro or VBA -- to do the import, a macro will work fine for doing
the TransferSpreadsheet. Which one is better may be more related to which
one you're most familiar with and knowledgeable about.