How to import external (.csv or .xls) data into an existing access table with relationships.
Posted on 2008-02-11
Hi, noob here again. I have about 250 records from another source that needs to be broken down (which I have done) and imported into an existing Access 2007 database. Different fields need to be imported into the three tables but the data needs to remain related.
I have documented the database relationships, then broke them and tried to import the first set of data, either .csv or .xls. I can't get it to work, either subscript out of range popup, or x items were deleted and 0 items were.... do you want to continue.
Yes, then 0 records are imported..
To set a baseline I exported a table from the existing database to a new access database for testing and tried it there. Same thing. I even went as far as turning off any required fields etc. still the import didn't work. Researching on EE I found a recommendation for using a linked to excel then query append. That got 1 record in. Auto number then messes up though for the other that didn't populate. I have to keep the autonumber consistend across the 3 related tables as that is the basis for the relationship. (I didn't design the database, I inherited it). Any I found that I could cut and Paste Append from one table to another. I tried that and it worked fine.
What is the best practice to keep the autonumbers consistent on the import to database or suggestion on how to keep things related on the existing database and the imported data. The auto number is what defines our job numbers in real life.
What is the trick to using the import feature of Access from .xls or .csv as nothing I tried worked.
I know the fields have to match exactly, but does order matter?
Should I include a field for autonumber in the import with no values, or do not include the autonumber field in import.
I did find code to reset or set the autonumber, so repost of that info is not needed.