Need help to migrate A2007 DB from old to new server 1

[TheresAlwaysAWay]

I have an Access 2007 db that has been happily working with SQL Server 2012. We have purchased a new server with SQL Server 2016. The database files were copied several days ago and I want to redirect a copy of the db to the new server for testing. Once testing is complete and we're sure all is well we'll wait (perhaps over a weekend), copy all the current data to new server, and then redirect all work stations to it.

I tried changing the ODBC connection by clicking External Data>Import>More, and I connected to the new server, yet all the data remains as most current. When I hover the mouse over any table's listing in the Navigation Pane, it shows a path to the old server. The ODBC connection has been redirected, but it seems that I must delete every table from the old server and then re-import each from the new server. This is a major task, and I'm pretty sure that there must be a way to just redirect all tables to tables with the same names on the new server, but I don't know what that method would be.

Can someone please explain the correct process? I am hopeful that this is not a long, tedious process of manually deleting and re-importing each table.

As always, thanks in advance for your assistance.

 

[dhookom]

I typically use the code from Doug Steele's web site DSN-less connections.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

Thanks, Duane. I appreciate the help.