Never Miss a Beat: Get a snapshot of the issues affecting the IT industry straight to your inbox.
Office Letter: Move Microsoft Access Data Between Tables
If you want to get data from one table into another in MS Access, use the Append Query function to do the job.
3 Min Read
This Office Letter tip comes courtesy of Jim Boyce: I recently needed to set up a database for a list server application. The list server created a Microsoft Access database with its own table and record layout. The problem was that the existing data was in another table, in another database, and in a different record layout. I needed to match the record fields in the source table with the record fields in the second table and migrate all of the data to the new table. If you’ve ever wanted to move data from one table to another, here’s how to make it happen:
Let’s assume that (as in my situation) you are working with two separate database files. The first I’ll call source.mdb and the second destination.mdb. Also assume that destination.mdb already contains the table in which the incoming data will be placed. You should make a backup copy of each database file just in case things go awry.
Start by importing the table from source.mdb into destination.mdb:
After making backup copies of the database, open destination.mdb in Access and choose File/Get External Data/Import.
Select the database to import (source.mdb in this example) and click Open. In the resulting Import Objects dialog box, choose the tables, queries, and other objects you want to import and click OK.
With the source table imported into the destination database, the next step is to create an append query that will move the records from the source table to the destination table.
Access doesn’t create queries as append queries by default, but it’s easy enough to change the query type. Here’s how:
In Access, click Queries in the navigator, then double-click Create query by using wizard to start the Simple Query Wizard.
In the wizard, select the source table from the Tables/Queries drop-down list. Select the fields you want to import into the destination table and click the > button. Click Next, choose Modify the query design, and click Finish. Access displays the query design window. Choose Query/Append Query to open the Append dialog box. From the Table Name drop-down list, choose the destination table and click OK. For each field, look in the Append To row. Select the field to which the current field should be appended. This is the step where you associate the source field with the destination field. After match all necessary fields, close the query and save changes to it.
At this point you’re ready to run the query. Double-click on it to start the process. Access displays a warning that it is about to update records in the table. If you’re comfortable with the query settings you’ve configured and have a backup on hand of the databases just in case, click Yes to perform the query.
When the query is complete, the data should be in the destination table.
The Office Letter is a weekly e-mail and online newsletter offering tips, tricks, and techniques for Microsoft Office. It offers shortcuts, explores features, and boosts productivity with hands-on how-to information for Word, Excel, Outlook, PowerPoint, and more.
About the Author
You May Also Like
More Insights
