asked on

Merge Access Databases

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?

Tom

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

peter57r

How do you identify which records have now been updated?

lbsm

ASKER

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

lbsm

ASKER

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.

GRayL

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.

lbsm

ASKER

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.

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial