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

Posted on 2008-02-11
Medium Priority
Last Modified: 2013-11-29
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.


Question by:pturner3
  • 3
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 20870573
<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.

Author Comment

ID: 20870679
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.
LVL 85
ID: 20870926
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:

ID  lName       fName  
1    Clampett  Jed

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.
LVL 85
ID: 20870932
Sorry, the example tables are incorrect, should be:

ID  lName       fName  
1    Clampett  Jed

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


Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question