Merge Access Databases

Posted on 2008-11-08
Medium Priority
Last Modified: 2012-05-05
I need help merging two databases. I have an access database that has address, city state zip, for my subscriber list. I recently had all the addresses updated and need to merge only the new addresses back to the original file (only 1000 have to be changed). The original file has a unique key and the appended file is in excel with the unique key as one of the columns. I want to keep the original unique key the same.  Is there a way to do this without cutting and pasting all of the new data?
Question by:lbsm
LVL 10

Expert Comment

ID: 22911827
Yes very possible, if you use some vb script. Let me know if this is your level
LVL 77

Expert Comment

ID: 22911830
How do you identify which records have now been updated?

Author Comment

ID: 22911835
i just use the program, I am not a software engineer. if you give me step by step instructions I could figure it out.
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Author Comment

ID: 22911837
with regards to which records have been updated, the excel file has a code in a new column that identifies what has been changed on the record.
LVL 44

Expert Comment

ID: 22913469
The Excel file with the 1000 or so records are all changes - right?  The unique key in the Excel table with the new address is identical to the unique key in the Access table with the old address - right?  Why not just link to the Excel file, so Access 'sees' it as a table and do an UPDATE query using an INNER JOIN:

UPDATE AccessTable AS ac INNER JOIN ExcelTable AS xl  ON ac.Key = xl.key SET
ac.add1 = xl.add1,
ac.add2 = xl.add2,
ac.city = xl.city,
ac.region = xl.region,
ac.country = xl.country,
ac.zip = xl.zip;

The field names may not be 100%, but you get the idea.

Author Comment

ID: 22916020
can you write out the steps to implement your solution. I think you want me to create a new query in design field, but I am lost after that. thanks.

LVL 44

Accepted Solution

GRayL earned 500 total points
ID: 22917179
You talk about merging two databases - don't you really mean two tables in the same database?  How do you get the Excel spreadsheet data into Access?  If you Link to it as I suggested then you need only to create the update query I suggested above  You need to identify the name of the excel table, the name of the larger Access address table and the field names that you want involved in the transfer.  

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

864 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