Appending Records to Table with an AutoNumber ID field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work with databases on the fly. I import part of the data, then import the
rest in a separate, successive operations. I start with a text file. I import
the data using the import data wizard, letting Access provide an Auto-Number
ID. When I attempt to add additional records using an identically formatted
text file, I get en error message. Essentially, I don't think the program
likes that I have not provided ID's for each of the 90,000 or so records I'm
adding. Is there a way to do this wihtout resorting to programing and such?

Thanks
 
If the table you are using is not used in any relationships or you don't have
any other use for the autonumber field, the easiest way to do it is to not
let Access create it. There is no way to do what you want without at least
some coding.
 
Instead of using the import wizard for the subsequent imports, you can
use either of these approaches:

1) create a linked table connected to the new text file, and use an
append query to move the data from the linked table into the table where
you need it. Don't include the autonumber field in the append query: the
numbers will be assigned as the records are appended.

2) Use an append query that gets its data from the text file without
using a linked table. The syntax will be like this:

INSERT INTO MyTable (Field1, Field2, Field3, Field4)
SELECT Field1, Field2, Field3, Field4
FROM [Text;HDR=No;Database=D:\File\Folder;].[Filename#txt];

If the text files have field names in the top row, use those names and
specify "HDR=Yes".
 
Back
Top