Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need help to migrate A2007 DB from old to new server

Need help to migrate A2007 DB from old to new server

Need help to migrate A2007 DB from old to new server

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.

RE: Need help to migrate A2007 DB from old to new server

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

Hook'D on Access
MS Access MVP 2001-2016

RE: Need help to migrate A2007 DB from old to new server

Thanks, Duane. I appreciate the help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close