How to import external (.csv or .xls) data into an existing access table with relationships.

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.

Thanks!



pturner3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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).  >

There's nothing at all wrong with using Autonumbers as Primary and Foreign keys ... it's pretty standard practice.

It appears that you're importing Parent/Child records of some sort ... if that's the case, then you'd need to import the Parent Records first, then import the Child records. Depending on how the incoming data is formatted, this can be a difficult task and if the incoming data isn't properly normalized/related then your job is even more difficult. Without seeing some sample data it's hard to advise further, but I've always found it much easier to do this via VBA than with queries, unless the data needs are somewhat simple.

Remember too that you can write directly to an Autonumber field; you don't have to let Access assign the number for you, so you can maintain data relationships IF your incoming data is related in that manner.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pturner3Author Commented:
Hi, thanks for the insight on the autonumber field and the child records.  The data on the import side uses a string of 16 chars for relationships. It's easy enough for me to figure out how to make these numbers for autonumber.  I didn't know I could use my own numbers for autonumber (I leave the field set for autonumber though right) that's a great tip.  Getting the child data up first totally makes sense, plus it's less complicated.  

I am dissappointed in the way Access imports and the error messages are totally useless.  I'm not a VBA programmer and don't have the background to whip up some code for importing.  I'm still trying to do it with cut and paste append as that seems to be the most forgiving.  I'll use your suggestions above and let you know the outcome.
Cheers!
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Well, the error messages do make sense, once you break them down ... it's just that there can be so much "crossover" between the messages that they don't seem to be working with the correct data sometimes.

We might be talking about the same thing, but generally you must import any data which will be used as Foreign Key fields FIRST ... in most cases, the ID of a Parent record is stored in the CHILD record, not the other way around so you'd have to import the Parent record first. for example:

tParents
------------------------------
ID  lName       fName  
1    Clampett  Jed

tChildren
ID   ParentID   lName       fName
1         1          Clampett   Ellie-Mae
2         2          Bodine       Jethro

Notice that I store the PARENT ID with the Children records ... so in this case, I'd have to import tParents first.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry, the example tables are incorrect, should be:


tParents
------------------------------
ID  lName       fName  
1    Clampett  Jed

tChildren
ID   ParentID   lName       fName
1         1          Clampett   Ellie-Mae
2         1          Bodine       Jethro

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.