Merge Access Databases

Posted on 2008-11-08
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 9

    Expert Comment

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

    Expert Comment

    How do you identify which records have now been updated?

    Author Comment

    i just use the program, I am not a software engineer. if you give me step by step instructions I could figure it out.

    Author Comment

    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

    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.region = xl.region, =, =;

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

    Author Comment

    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now