Importing Tab delimited file to Access. Getting error if I save an import specification

Question

I have the following tab delimited file:

And this Access table:

When I go through the "Get External Data - Text File" procedure, everything works. But, when I go through the same procedure with the added step of saving a specification file (because I want to automate this task), I get an error because it is not putting my data into the right fields.

here is my specification file:

And here is the error I'm getting:

And here's what happens (data in the wrong columns):

Any help would be greatly appreciated!

Answer 1

I think the AutoNumber ID field is your problem here, being in that it lies in the middle of your incoming literal data. Note the error message talking about "key" violations.

If you must have an AutoNumber field in your base table when you are importing then make sure it is positioned to the right of the very last data coming in, so it doesn't get stomped on. In other words if you are sucking in:

a  b  c
d  e  f

That's ok, so long as the table you are sucking it into is:

field1  Text
field2  Text
field3  Text
field4  AutoNumber

But this incoming data will generate an error into the above table:

a  b  c  x
d  e  f