How can I replace one Access data connection with a different connection that contains identical data and structure?


New Member
Oct 16, 2013
Here's my situation.
I have a workbook that has a table and a pivot table that are both connected to a single Access database file. I'll refer to this database file as "Old".
There are formulas on other sheets in the file that reference the tables.

I also have another Access database file that has exactly the same fields, structure, etc. I'll refer to this database file as "v2".

I now need to switch where the tables are pulling the data from, from Old to v2. I don't want anything else to change within my workbook if I can help it (ie. all formulas must continue to work and not get #Ref errors).

What's the most efficient way to do this? Is it even possible, and can I do it without building the tables all over again and then having to fiddle with finding all of the formula references and updating them to the new table?

If I do have to rebuild the tables and then update the formulas, can someone suggest an efficient and methodical way of doing it? I have a number of separate workbooks I'm going to need to migrate to v2.

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics