• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Moving Access Split Database (Access:FrontEnd MS SQL:BackEnd) to a New Server


We have a database running on Access 2007 that is split with the front-end in Access and back-end in MS SQL 2008.  This works great and we love the flexibility of the Access GUI and the stability of the SQL data.  However, we are maxing out our current servers capacity and want to move the back end to a new server to improve performance.

The question is how to move the back-end and relink the tables.  The best approach we have concocted is to use Access 2007 to Copy both the Structure and Data of each table to create a new local table within our Access application.  This we would do for each table (there are about 25 tables so not an enormous job).  We then think that we would delete all of the old linked tables as well as the old SSMA local tables.  Following that, we would then use MS SQL Server Migration Assistant 2008 to "re-split" the database to the new server.  

This method sounds a bit clunky and I am not sure that once the linked tables are deleted that the Access application will run properly as it may not be pointed to the correct location of the data tables.   I am hoping that there is a more elegant method that MS has embedded in either their Migration Assistant or in SQL Server Manager Studio.

If that sounds correct then give me your blessing.  If not, then how should this be done safely without destroying our precious data.

3 Solutions
Anthony PerkinsCommented:
Surely it would be more efficient and faster to do either:
1. Backup the old database and restore the old database on the new server or
2  Detach the database, copy the database files to the new server and attach the database.

Then all you have left is to relink all your tables in MS Access to the new server.
isurgynAuthor Commented:
OK sorry I didn't mention that we have already backed up the old database and restored it to the new server.  That was our first step and seemed rather obvious.  However, when we attempt to use the Linked Table Manager in Access 2007 to link to the new location of the tables, although there is a check box to ask to browse to new tables, it doesn't seem to work properly as it gives us an OBDC error message.  I am not in front of the computer right now so don't recall the exact error but it is very short...maybe 5 words or less.

The Linked Table Manager in Access is pretty spartan.  No controls, no buttons except this one check box.   Without providing Access with the new location of the tables, it simply relinks the current tables on the "old server" (which we are still live and using until we get the new server operational).

So any ideas on what we are doing wrong as we must be missing something really simple here.
Make sure you check the box 'Always prompt for new location' in the Linked tables manager.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In my experience, in most cases you are better off deleteing the LINKED tables and recreating those links. There is not really a builtin method to do this in a single step, however you can run code like this to remove ALL linked tables from your database:

Function RemoveLinks()

Dim tdf As DAO.TableDef

For each tdf in Currentdb.TableDefs
  If Len(tdf.Connect) > 0 Then
    DoCmd.DeleteObject acTable, tdf.Name
  End If
Next tdf

End Function

Copy/paste that function into a Standard module, then "run" it from the Immediate window like this:


This will delete ALL LINKED TABLES, so if you have tables from different sources (which is not common, but does occur) be sure you know what tables are to be linked to the appropriate backend source.

Once you've done this, just use the External Data ribbon item to create a new set of linked tables to your new SQL Server database.

Also, did you use a DSN when doing this? If you did, then obviously you'd have to rebuild the DSN to point to the new server (or build a new DSN and use that one).
isurgynAuthor Commented:
Thanks for all the input.  I did try the Linked Table Manager again with the Always Prompt for New Location checked and we are no longer getting the ODBC error.  Not sure why when we tried that before we got the error.

However it does now open a new dialog box that asks to rebuild the DNS.  Originally we split the database with MS SQL Server Migration Assistant 2008 so that may have automated the DNS configuration.  

Any tips on how to configure the DNS?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the ODBC applet in control panel to configure your DSN, but you'll continue to have troubles with this until you remove and re-create the links, as suggested above.
isurgynAuthor Commented:
OK thanks.  We will work on this plan and get back with the results.  I am going to be out of the office so may be next week before I can work through this to its endpoint.
isurgynAuthor Commented:
OK we did reconfigure the DSN, performed the backup and restore operation and relinked the tables.  The new server is working beautifully.  We have been running now for several days without any problems so there does not seem to be a need to delete the tables as suggested.

Thanks so much for the help.  The solution is perfect.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now