Import Related
By Richard Rost
3 years ago
Import Preserving Relationships, AutoNumbers
In this Microsoft Access tutorial I'm going to teach you how to import data from multiple tables in another Access database, preserve relationships between those tables, and also maintain the integrity of your AutoNumber keys.
Prerequisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, preserve relationships of imported data, Import Relationships, Import Tables that have relationships, import microsoft access related tables with autonumbers, import data already has key
Subscribe to Import Related
Get notifications when this page is updated
Intro In this video, we will work with Microsoft Access to learn how to import multiple related tables from one database into another while preserving their relationships and autonumber IDs. I will show you how to assign temporary IDs, update foreign keys to keep links intact, and use update and append queries to safely transfer your data. You'll see step-by-step instructions on managing parent and child tables, handling foreign key conflicts, and cleaning up after the import process, helping you avoid common pitfalls when merging linked tables in Access.Transcript Today's video is a continuation of yesterday's video.
Yesterday I showed you the beginner stuff - how to import data from one Access database into another - but we only did a single table. Today, we are going to get a little more complicated. We are going to import multiple tables and preserve their relationships and their autonumbers.
Now, if you are only importing one table, just customers, then that customer ID really isn't that important. Access uses that customer ID to make sure that you have a unique identifier for each customer. As long as it's just one table, it doesn't matter if Frodo is ID 1 or ID 5. It does not matter unless you are bringing in two tables from one database into another.
If that's the case, over in database 1, Richard, me, I have ID 1 and I have my contacts down here. James is 2. He has his contacts. James has contacts. 3 is Deanna. She has hers.
In the new database that you are importing, Frodo is customer 1. He has his contacts that are linked to customer ID 1. Gandalf is 2. Legolas is 3, and so on. Here is the problem. We can't just import this because now these people are going to get assigned new customer IDs and that is going to throw these off.
So what we have to do is, before we do the import, we have to assign a temporary ID to all the new customers. It does not matter what it is, as long as it does not exist already in the customer table. So find whatever your largest customer ID is, maybe add a thousand to it, whatever. In fact, it could just be a random number as long as it's unique.
Then what we're going to do is we're going to change that ID in the foreign key of whatever our subtable is. In this case, we're going to do contacts. We have customers and contacts, but this doesn't matter whether it's customers and contacts, customers and orders, orders and details, products, whatever. As long as you do it with the parent table and the subtable in this way.
Now, there are a few steps. It's not easy, but I am going to walk you through it step by step.
Alright, so here I have two copies of my database. Here's database one. You can see customers. Here, me, James, the standard TechHelp template. I am just going to slide this over here like this to get it out of the way. We can close that.
Okay, here's database two. I changed the data in here a little bit. We have customers. We have Fred O'Geneff, Legolas, and Paragon. Notice they have customer IDs one through four. I did that intentionally, and they're related to contacts with their ID, and they also have contacts one through four, but that doesn't matter because this is a parent table that doesn't relate anywhere else. What we care about is this foreign key right here, okay.
The order table, you could do the same thing, but again, you have to keep track of the order detail table. So every table, you have to do it with its subtable as well. If you have lots and lots of tables, this is going to take you a while. Unfortunately, you should only have to do this once. Once you do this and you're done importing your data, you are good.
Alright, so step one - we are going to bring over, we are going to import the tables that we want. We are going to make them customer T2 and contact T2. So here is the customer table and contact table. You can select them both, click, drag, and drop. We are going to bring over structure and data, customer T2. We are going to delete them when we are done, so don't worry about the names. Contact T2.
Alright, so we have copies of those over here. Now we can change this. I forgot one very important thing first. Back up your data. Back everything up. Your old table, your new table, your whole hard drive, your mom's hard drive - back everything up. Back up, people. Watch my backup video. I do not want anybody complaining to me that you did this and it messed everything up and now you lost data. No. Back everything up.
Alright, so now we just work with this database. We can put database two aside and all data is in here.
Alright, we need to add a temporary ID to this table because we are going to have to reassign this person. So come into design view, come on to the bottom here. We are going to add a temp ID. That will be a number of type long integer. We are going to bring it up top so it's easier to keep track of. Where did you go? I am here. Click, right, get up, put it right under customer. Save that. Take a peek at it.
Now we want to put a number in here that is going to uniquely identify this person. It doesn't matter what - you could even just add to the customer ID. You want to make it something that is not in the customer table. So find out whatever your largest ID is over here. In this case, it's 29. So I will just add a thousand to it. Because later on, these are going to come together. You want to make sure that these are not in the customer ID list as well.
Now, if you only have four records, you could just type these in. But I will use a little update query. We will pretend we have thousands of records in here. So we will go to Create, Query Design. We will make an update query. What are we updating? We are in customer T2. We are updating the temp ID. What are we updating it to? We are going to make it customer ID plus a thousand, just like that. If you do not know how to do an update query, go watch this video.
Alright, now I can run this. I do not see any messages because I have warnings turned off. But if I go look at my data now, you will see there, I have values there. That is my temp ID.
Alright, close this. We do not need this query anymore.
Now, what I want to do is in the contact table, that is my foreign key, but it is pointing to customer T2. So I am going to change that to the new temp ID. When I import these into the other contact table, there are no conflicts with the existing customer ID.
Alright, so we have to change that. We are going to make another update query. Create a query design, make an update query. This time, I am going to bring in my contact T2. I want to change my customer ID, but the data is in the other table. The data is in my customer table. Right? It is this one. I want to change the customer ID here.
Notice we are related to the customer ID over here. That is fine. We are going to update it to whatever that temp ID is. Temp ID. Make sure it does not do that - see what it did there? It put quotes around it. We don't want quotes. We want the field temp ID, with brackets. Here, I will zoom in so you can see it. Brackets, okay.
It is going to change that customer ID, which is the foreign key in the subtable or the child table, to that temp ID that we just made.
Someone is beaming in, hold on a second. Energize.
Okay, run it. Again, nothing appears to happen, but take a look at your data in the contact T and look at that. Now, the foreign key is changed to that temp ID, so we are good. Now, if I were to import these contacts into the other table, there would be no conflicts because we have a new customer ID.
We are not done though. We have to keep going. We are about halfway there.
Close this - we don't need this one anymore.
Now, I am almost ready to bring these records into my main customer table, but I need a place to hold that temp ID for a minute. So we are going to add it to this table too. So, design view, and add the temp ID here. It is going to be a number of type long integer because we are assigning it. Save it. Again, I am going to slide it up top just to keep track of it. Save it. Close it. These should all be blank right now. That is fine. Leave them alone.
Alright, next, I am going to delete the autonumbers from my import tables, because at this point, I do not need them, and it just makes the append query easier. We are just going to delete this because we do not need it anymore. We have that temp ID that is holding the relationship.
So, delete the customer ID from customer T2. Are you sure you want to delete the primary key? Yes. So it looks like this now. We have our temp ID. We are good. Then our contact T2, we can get rid of this because we do not need it. It is not related anywhere in any other tables. Delete. Yes. Yes.
Now, I am ready to copy that data over with two append queries into the original tables. If you have never done an append query before, go watch this video.
So, we will do the customer table first. Create Query Design. We are going to make an append query. We are appending into customer T. Hit OK. What are we putting into customer T? The data from customer T2. Bring down the star, which I can because I deleted the customer ID - that is why I wanted to delete it, so I don't have to bring all the other fields down. Run it. Nothing appears to happen. Again, I have my warnings turned off. Close it. Take a look at your customer table now. Scroll down to the bottom. Look at that. There are our four new records. They were assigned new autonumbers. That is fine. There is the temp ID that we are going to use in a second to put these new autonumbers in the contacts.
See where I am going with this? Hold on. We are going to get there.
Let's do the same thing with the contact table. Create Query Design. Append into what table? We are appending into contact T. Hit OK. What records? Contact T2. Bring down the star. Run it. Everything should be good. Close it. Don't save it. Take a look at your contacts. Scroll down. There they are.
Now, the next step is to convert those into the customer IDs from this table. We have to set it to 30, 31, 32, 33, and then get rid of the temp ID.
So, let's delete the customer T2. We do not need it anymore. Goodbye. So we are not going to be confused by having all these tables. Delete that one too. Goodbye. All the data we need now is in these two tables.
Now it's time for another update query to change that customer ID back to a normal autonumber. Well, it's a foreign key, so it's a long integer, but we are changing it to the autonumber that the customer was assigned. Makes sense?
Again, update query. Create Query Design. We are going to bring in the contact table and the customer table, but it joined by customer ID. That is not what I want. If you look at the table, I want to join it by this temp ID right now, okay? Join it to the temp ID so we know who is who.
So delete that relationship and join this customer ID to the temp ID over here. Click, drag, drop on the temp ID. Now, if I look at these contacts, here is contact and customer and first name. If I take a peek at it, just to look at the data - there we go. That is what I want. It is joined by this.
Get rid of this stuff. That's the correct join.
Now, we are going to change this to an update query. We are going to update this foreign key. We are going to set it equal to this new customer ID over here. Now, we can't just type in customer ID because there are two tables, so we have to type in customerT.customerID.
Run it. Again, nothing appears to happen. Close it down. Take a look at your contacts. Scroll to the bottom. Look at that. There are those four contacts, the hobbits and Legolas and them, and look at their customer ID is now lined up with this customer ID - 30 through 33, same - because we swapped out that ID.
Now you can delete that temporary ID because we do not need it anymore. Come in here, delete this, and you have now imported your tables.
Go to the customer form, go to the customer list, scroll down, there are your hobbits. Frodo, Paragon, Gandalf, and Legolas. Frodo is not a hobbit, but you know what I mean. Open up Gandalf, go to contacts, and there is his contact. Look at that. You did it.
Yes, you may have to watch this video two or three times and take some notes. There were about ten steps there, but that is what you have to do.
In a nutshell, you have to create a substitute ID, bring in those records so you maintain the relationship without stepping on the IDs that are already in the other table. Once you have those in there, swap that ID back out again, get rid of your temp ID, and assign it to the new customer ID that was assigned to it. Do the same thing with orders. Make sure you do order details too.
That is basically it. I wish there was an easier method to do this. I can't think of one. I thought about maybe using something like JSON or even XML because you can export multiple tables there and preserve the relationships, but that gets even trickier with the different data types and all that. This is the easiest way I found to do this.
Could this be automated with some VBA? Sure. It would be a project and a half. If you really, really want to see it, let me know, and I will cover it in a developer lesson. It won't be a TechHelp video and it will be two hours long, but I'll do it in developer class.
This is generally something that people do not have to do more than once. This was something that when Deborah sent me her info (and she was the woman from the original video), I thought it was just customer information. She said, yeah. I said I will make a video on that, and I just knew it would be more than that. And of course, a couple of days later, she said she had to bring in all this stuff too.
So I knew I was going to need to make a second video. So here is the second video. Deborah, I hope this helps you.
Again, watch the video a couple of times if you have to - that is the benefit of a video. You can watch as many times as you want and make some notes. If you have any problems or questions, post them in the forum and my awesome guys will try to help you.
If you want to learn more about this stuff, there are two main topics that really will help you understand this better. First is these action queries, and I have five classes that cover the different action queries in Access: update, append, delete, make table, and all the other weird ones too. I have lots of classes, lots of videos on these action queries. That is very important for doing this kind of stuff.
The second thing is my relationship seminar, understanding relationships between different tables, how they work, how they're set up, IDs, keys, foreign keys, primary keys, all that stuff. Joins. That's all covered in the seminar. There's tons of stuff covered. It's about four hours long, all the different kinds of relationships you can possibly think of. If you would like more information on that, you will find links down below.
That is going to be your TechHelp video for today. I hope you learned something. I hope you watch the video again so you understand it. This took me a while to figure out the first time too. I had to do this for a client about 20 years ago, and he had like 30 tables to import. They merged about four offices together. It was a lot of work. It took me a weekend, but once I got the hang of it, it became second nature.
Yeah, it is not easy, but that is how you do it. If anyone has a better solution, I want to hear about it. Post something in the comments down below.
Live long and prosper, my friends. I will see you next time.Quiz Q1. Why does importing multiple related tables from one Access database to another require special attention to customer IDs?
A. IDs are only important for single-table imports
B. IDs represent customer names, which must match exactly
C. IDs are used to maintain relationships between tables, and importing may cause mismatches
D. IDs are not affected when importing multiple tables
Q2. What is the main risk if you simply import related tables without adjusting IDs?
A. Duplicate tables will be created
B. Data types may not match
C. Relationships between records will be broken due to new IDs being assigned
D. The tables will remain locked
Q3. What is the purpose of creating a temporary ID field during the import process?
A. To convert all numeric fields into text fields
B. To ensure each imported record can be uniquely identified and relationships preserved
C. To delete unnecessary data from the table
D. To speed up the import process
Q4. When assigning values to the temp ID field, what is the most important requirement?
A. Values should be sequential starting from 1
B. Values should match existing customer IDs in the main table
C. Values should be unique and not exist already in the customer table
D. Values should be random and duplicated
Q5. How do we update the foreign key in the subtable (for example, contacts) to preserve relationships?
A. Update it to refer to the primary key of the main customer table
B. Leave it unchanged, as it will update automatically
C. Update it to the value in the temp ID field from the parent table
D. Change it to zero
Q6. After copying the temp ID values and updating foreign keys, what must you do before appending records to your main tables?
A. Add new fields for every data point
B. Delete the autonumber primary keys from the import tables
C. Rename the temp ID field to match the original field
D. Recalculate all existing IDs
Q7. Why do we delete the autonumber fields from the temp (import) tables before running append queries?
A. So that MS Access will automatically assign new autonumbers when importing into the main tables
B. To avoid increasing the table size
C. To speed up the import process
D. To convert all data types to integers
Q8. What do append queries do in the context of this import process?
A. Copy table structure only
B. Add new fields to existing tables
C. Add (insert) the imported records into the main tables, generating new autonumber IDs
D. Remove duplicate records from a table
Q9. After imported records have received new autonumber IDs, how do you re-link the contacts (subtable) to their corresponding customers?
A. By matching contact first names with customer first names
B. By updating the foreign key in contacts to match the new autonumber in customers, using the temp ID as a reference
C. By updating the temp ID field to zero
D. By deleting all existing relationships
Q10. Once all data is imported and relationships restored, what should you do with the temporary ID fields?
A. Make them the new primary keys
B. Leave them empty for documentation purposes
C. Delete them from both tables
D. Rename them for future use
Q11. Which type of queries are essential tools for this multi-table import process?
A. Make-table and select queries only
B. Action queries, including update and append queries
C. Delete queries only
D. Parameter queries only
Q12. What should you ALWAYS do before attempting this kind of import and data manipulation process?
A. Disable all Access warnings
B. Back up all data, including old and new databases
C. Change all field names to lowercase
D. Restart your computer
Q13. According to the video, why is this method preferred over advanced methods like exporting to JSON or XML?
A. JSON and XML are too fast and may skip data
B. Access does not support importing JSON or XML
C. Handling multiple tables with different data types in JSON or XML is riskier and more complex
D. This method automatically generates VBA code for you
Q14. If you have related tables such as orders and order details, what must you do when importing?
A. Only import the parent table
B. Repeat the temp ID and foreign key process for both tables
C. Merge both tables before importing
D. Ignore any IDs, as Access will handle them
Q15. What is the main conceptual purpose of the described import process in the video?
A. To separate databases into smaller tables
B. To preserve and correctly re-establish relationships between records when merging data from multiple Access tables
C. To convert all fields to text type
D. To automate report generation in Access
Answers: 1-C; 2-C; 3-B; 4-C; 5-C; 6-B; 7-A; 8-C; 9-B; 10-C; 11-B; 12-B; 13-C; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone is a follow-up to yesterday's lesson, where I showed you the basics of importing data from one Access database into another, focusing on just a single table. In this session, we are going to explore how to import multiple related tables while preserving both their relationships and their autonumbers.
When you are only importing a single table, such as customers, the customer ID field serves simply as a unique identifier for each row. In this case, it does not matter if Frodo is customer 1 in one database and customer 5 in another, as long as each row remains unique. However, if you need to import more than one table, such as customers and their related contacts, things become more complex.
Consider this: in your original database, Richard has a customer ID of 1 and a set of related contacts. James is ID 2, with his own contacts, and so on. In your new database, Frodo is customer 1 with his own contacts, Gandalf is 2, Legolas is 3, and so on. The trouble arises when you try to move both tables—those customer IDs are likely to get reassigned during import, which would break the relationships between customers and their associated records.
To prevent this, you need to create a temporary ID for all the new customers you are importing. This temp ID can be any unique number not already used in your main customer table. For example, you might add a thousand to your existing largest customer ID or pick any unique value that does not conflict with what's already present.
Once you assign a temp ID to every new customer, you then update the related records in your subtable—in this example, the contacts table. Update their foreign key to point to this new temp ID rather than the original customer ID. This approach can be applied to any related parent/child tables, such as customers and orders, orders and order details, or products and related tables.
The process involves several steps, but I am going to take you through each one in detail.
First, make sure to back up everything before proceeding: old tables, new tables, your entire database—ideally everything on your hard drive. You cannot be too careful.
With your backups safe, start with two copies of your database: one serving as the source with the data you want to import, and the other as the destination. In the source, identify the tables you need to move, such as customers and contacts. Import both tables into the destination database with temporary names, for example, customerT2 and contactT2.
Next, enter design view for the imported customer table and add a new field called tempID as a long integer. Move it near the top for clarity. Now, assign a unique value to tempID for each record. If your largest customer ID is 29, you can just set tempID to customerID plus 1000. For large data sets, set this value using an update query.
Then, update the corresponding foreign key values in the contacts table. Still working in the imported contactT2 table, create an update query that sets the customerID field to the corresponding tempID from customerT2. This ensures each contact correctly points to the new temp identifier.
After updating the foreign keys in your contacts table, return to your destination's main customer table. Add a tempID field there as well, again as a long integer, and keep it blank for now. The next step is to remove autonumber fields from your imported tables to avoid conflicts during the append process. Delete the customerID primary key from customerT2 and the contactID from contactT2, since the main table will automatically generate new autonumbers during the import.
Now append the records from customerT2 into your primary customer table using an append query. The tempID will be included for reference. Do the same for contactT2 and append the data into the main contacts table.
After importing, you will notice that your new customer records have been assigned new autonumbers. The next job is to re-establish the links between customers and contacts. At this point, you do not need the imported T2 tables anymore, so go ahead and delete them from your database to avoid confusion.
Now, execute another update query to realign the foreign keys in the contacts table. Join the contacts and customers tables using the tempID field, which connects each contact to its correct customer. Update the customerID in the contacts table to the newly assigned autonumber from the customers table.
Once those links have been reestablished, you can remove the tempID field from both the customer and contacts tables—it has served its purpose.
After all of these steps, check your data by opening the customer form or list in your database. Scroll down to the new records; you should see the imported customers and, when opening up their records, the related contacts should appear and be correctly linked. While the process is a bit involved, if you follow these steps you will preserve both your data and your relationships between tables.
It is worth reiterating that this process though a bit lengthy is typically only needed once. In my own experience, I had to do this for a client about 20 years ago when merging data from several different offices, and it took some time, but once I understood the method, it became much easier.
If you are interested in more details on the techniques involved, such as action queries—append, update, delete, and make-table queries—I have a series of classes covering each of them. Understanding how these queries work is essential for handling complex data tasks like this one. Additionally, my seminar on relationships between tables will give you a thorough understanding of keys, joins, and all the different ways tables can be linked together in Access.
I hope this tutorial helps you understand how to import multiple related tables into Access while keeping your relationships and autonumbers intact. If you have any questions or run into trouble, feel free to post in the forum for help. And if anyone out there knows of a better way to accomplish this, I encourage you to share your solution in the comments.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Importing multiple tables into Access and preserving relationships
Assigning temporary IDs to avoid key conflicts
Updating foreign keys in subtables to reference temp IDs
Using update queries to assign temp IDs
Using update queries to update foreign keys
Adding a temp ID field to tables in design view
Deleting autonumber primary keys before appending
Creating append queries to move data between tables
Using append queries to import data without key conflicts
Restoring correct foreign keys with update queries
Joining tables on temp IDs to update relationships
Cleaning up by deleting temp columns after import |
