Import Access
By Richard Rost
3 years ago
Import From Another Microsoft Access Database
In this Microsoft Access tutorial I'm going to teach you how to import data from one Access database to another.
Deborah from Coral Gables, Florida (a Platinum Member) asks: My company just merged two offices together. We have the same Access databases running each office, but now I need to import their customer table into ours. What's the best way to do that?
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, import data from another access database, Import or link to data in another Access database, data importing, import data from Access to Access, import a table from another database, structure only, structure and data, append data to existing table
Subscribe to Import Access
Get notifications when this page is updated
Intro In this video, we will talk about how to import data from one Microsoft Access database into another, focusing on the scenario of combining customer tables after a company merger. I will show you two different methods for copying a table from one database to another, including using simple drag-and-drop as well as the External Data import feature. We will also discuss some important things to consider after importing, such as checking for duplicate records and managing your imported tables.Transcript Today we're going to see how to import data from one Access database into another one.
Today's question comes from Deborah in Coral Gables, Florida, one of my Platinum members.
Deborah says, "My company just merged two offices together. We have the same Access database running in each office, but now I need to import their customer table into ours. What's the best way to do this?"
After some back and forth emails, Deborah told me they have the same actual Access database, but it's in two separate offices and they're not connected. So if they make design changes, they just send over the design changes, but all the data is still separate.
So now she's got to get office two's data into their office one database.
All right, so there are a couple of ways you can do this. I'm going to show you the easy way first. I'm going to tell you up front, the easy part is going to be getting their data into your database. Your part is checking for duplicates, but we'll talk about that later.
So here's the easy way. Open up your database. I'm just going to resize it so it's small and then we'll open up the new database. Again, I'll just slide this over here.
All right, here's your database. Here's their database. Now what I'm going to show you only pertains to one table. If you've got relationships in your database setup, then it's a little more complicated. We'll talk about that in tomorrow's video. But for today, I'm going to teach you how to do a basic import of one table.
All right, so here's how you do it. Here's their customer table. Here's your customer table. Click, drag, drop. You get this little window. It's this Paste Table As. It's just like you copied and pasted the table from one database to another.
Now there are three options.
Structure only. That means you just want the structure of the table with no data in it. No, we don't want that.
Structure and data. Yes.
Then there's append the data to an existing table. You can do this. It will bring all of their records right into your table.
Personally, I like to look at the table first and do some working on it and some fixing on it before I actually import it directly into my table. So I'm going to pick structure and data and give this a different name. Give this like customer2T.
All right. Hit OK. And there it is. It pulls it right in. If you just bring these records directly into your customer table, you don't get any chance to look stuff over itself. We'll talk about that in a minute.
All right. So I close this second database and let me resize this guy and make it big. Now I've got two tables here. I have customerT and I've got my customer2T.
Now what's the second way to do it? That's the easy way to do it. Let me show you another way to do it, and the second way's got more options.
Let me delete that table. Let's do it another way. This way involves going to External Data, and on the Import and Link here, you're going to go to New Data Source, From Database, and then Access.
This is where you can pull in stuff from different types of data sources like SQL Server, Azure, Excel files. I've got a whole separate video on importing from Excel.
But we're going to go Database and then Access, and you get this guy.
All right. The first step is browse to where your database is. Hit the Browse button. Mine's sitting on my desktop. It's right there, DB2. Hit Open.
Now you have a couple of options down here. The first option is import tables, queries, forms, all the different objects. You can import anything you want out of that database, not just the tables. So you can bring in the queries, the forms, whatever.
The second option is to link to those tables. That's like if you're splitting your database, you can link to different tables.
You don't want this now. This is an option you could use if you wanted to. You could link to that table and then do some manual importing using queries. We'll talk about that in a minute. But for now, I'm just going to import that table.
Hit OK. You can pick which table you want or multiple tables. If you want to get multiple tables, just pick this one. Hit OK.
If you plan on doing this more often in the future, you can save these import steps, but we're not going to do that right now. Say Close. There it is. It comes in as customerT1. Same thing, just put the 1 on the end of it.
There you go. Now you've got this data in here. Now the hard work begins. That was just importing the data.
Now you have to go through here and look for duplicates and all kinds of things like that.
I've got a lot of separate videos to teach you how to do stuff like that. You may want to start with an append query. This will bring the records from one table into another one.
I like to do it this way manually first because it will tell you if you have any problems, like how many duplicated records you might have, if you've got keys that are duplicated, if you have indexes that are duplicated, that kind of stuff. I talk about that in more detail in this video.
Once you've got them together, you can find duplicates and delete them if you have two duplicated customers. Or if you just want to see which ones are duplicates, you can use this video with a little conditional formatting. For example, say show me which ones have the same address.
If you want to learn a lot more about importing data, I cover it in my Access Expert Level 20 class. We cover importing from Excel, from text files, from HTML pages, all kinds of different options.
Now like I said earlier, that's how you import one simple table. What if you've got multiple tables with relationships, like customers to contacts, customers to orders, that kind of thing? We'll talk about that in tomorrow's video.
So tune in tomorrow, same Bat time, same Bat channel, and I'll show you how to do that one.
But that is going to be your TechHelp video for today.
I hope you learned something.
Live long and prosper, my friends.
I'll see you next time.Quiz Q1. What is the primary challenge when merging data from two identical but separate Access databases?
A. Maintaining identical user interfaces
B. Checking for duplicate records after importing
C. Ensuring SQL Server compatibility
D. Importing database design changes
Q2. What is the simplest way to import a table from one Access database to another, as described in the video?
A. Using a VBA import script
B. Clicking, dragging, and dropping the table from one database window to another
C. Exporting data to Excel and re-importing
D. Making a backup and restoring
Q3. What does the 'Structure only' option do when importing a table in Access?
A. Imports only records, not field names
B. Imports both structure and data
C. Imports only the structure with no data
D. Imports queries instead of tables
Q4. Why is it a good idea to give the imported table a different name when bringing it into your database?
A. It allows you to directly overwrite the existing table
B. It is required by Access
C. It helps prevent confusion and lets you review records before merging
D. It keeps the old table hidden
Q5. What feature does the External Data tab provide when importing from another Access database?
A. Only allows linking to tables
B. Allows importing all types of objects like tables, queries, and forms
C. Allows importing just queries
D. Only allows exporting data to text files
Q6. Instead of directly importing into your main table, why might you want to first import into a separate table?
A. It reduces the file size of the database
B. It provides an opportunity to review and clean up data before merging
C. It prevents Access from creating duplicate entries automatically
D. It automatically removes all duplicate records
Q7. What should you do after importing data into a separate table?
A. Immediately delete the imported data
B. Merge it into the existing table without review
C. Check for duplicates, keys, and indexes before merging
D. Re-import the data using SQL Server
Q8. What tool is recommended for bringing records from one table into another within Access after inspecting the data?
A. A backup restore
B. An append query
C. A delete query
D. The compact and repair tool
Q9. Which scenario is NOT covered in the video but mentioned for a future tutorial?
A. Importing from text files
B. Importing multiple related tables with relationships
C. Importing forms from another database
D. Using VBA to export data
Q10. Which import method offers more options than simply dragging and dropping tables?
A. Using the External Data - Import and Link - New Data Source method
B. Emailing the table and opening it
C. Copying and pasting data in Excel
D. Compacting and repairing the database
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-B; 10-A
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 focuses on importing data from one Access database into another.
Recently, Deborah reached out with a situation many organizations face. Her company has just merged two offices. Both locations have been using the same Access database, but since they operated independently, each has its own separate data. Now, Deborah needs to bring the customer table from the second office's database into the main database at the primary location. This is a common scenario whenever offices consolidate, and it's important to know the best approach to combine their data.
The process starts with the basics. Both offices used identical database designs, but data has not been shared between the two. So, Deborah needs to import the customer data from the second office into her office's system. There are a few ways to accomplish this, and I will explain the simplest method first.
The first, and easiest, way to import a table is by opening both database files side by side. If you only need to import a single table and your database doesn't make use of complex relationships, this method is straightforward. Simply locate the customer table in the second database and drag it into your main database window. When prompted with the Paste Table As dialog box, consider your options. You will be given three choices: to import the structure only, import the structure plus data, or append the data into an existing table.
If you want full control and the chance to review the records before merging them with your existing data, select the option to import both structure and data, and give the table a new name like customer2T. This prevents overwriting your existing table and allows you to check for issues before combining records. If you choose to append directly into your existing customer table, you will not have the opportunity to review or clean up the data before it gets mixed in. For that reason, I suggest importing it as a new table first.
Once the table has been imported, close the second database and return to your main one. You should now see two tables: your original customerT, and the newly imported customer2T.
There is also a second method if you want more flexibility, especially when importing multiple tables or different types of objects. Use the Import feature found under the External Data tab. Choose New Data Source, then From Database, and select Access from the list. This option allows you to pull in not only tables but also queries, forms, reports, or other objects from another database. You can also link to those tables instead of importing them if needed, but for simply moving data, the import option is what you want.
After you've browsed to and selected the database to import from, you will be asked to pick which tables or objects to add to your database. Once you've made your selection, the table will appear in your database with a slight name modification, such as customerT1, to avoid naming conflicts.
At this point, most of the work is done. The data is now in your database, but now it's time to deal with possible issues like duplicate records or conflicting keys. This is where things can get tricky. You might use an append query to bring the new records into your main table. Doing this manually allows you to spot errors, such as duplicate keys or indexes and lets Access inform you of any issues before merging everything.
After merging, you should also search for duplicates or unwanted records. There are various techniques and additional videos that discuss finding and cleaning up duplicates, as well as using conditional formatting to flag potential problems, such as customers with the same address.
If you wish to learn more about importing data, including from Excel, text files, and HTML, all of that is covered in greater depth in my Access Expert Level 20 class.
The examples here focus on importing just a single table. If your database relies on multiple related tables, such as customers linked to orders or contacts, the steps become more complex, and I will be covering that in a future lesson.
That covers the basic process of moving a table from one Access database to another. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List Importing a table from one Access database to another
Copying and pasting tables between databases
Using Paste Table As options in Access
Importing structure and data vs structure only
Renaming imported tables during import
Importing tables using External Data functions
Using New Data Source from Access database
Selecting specific tables to import
Understanding the difference between importing and linking tables
Saving import steps for future use
Managing duplicate records after import
Using append queries to merge data
Detecting and handling duplicate keys and indexes after import |
