Solved

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

Posted on 2011-09-22
8
284 Views
Last Modified: 2012-05-12
Hi,

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.

Thanks.
0
Comment
Question by:isurgyn
8 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 36584479
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.
0
 

Author Comment

by:isurgyn
ID: 36584984
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.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 100 total points
ID: 36585358
Make sure you check the box 'Always prompt for new location' in the Linked tables manager.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 84
ID: 36586342
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:

?RemoveLinks

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).
0
 

Author Comment

by:isurgyn
ID: 36587556
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?
0
 
LVL 84
ID: 36587924
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.
0
 

Assisted Solution

by:isurgyn
isurgyn earned 0 total points
ID: 36588193
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.
0
 

Author Closing Comment

by:isurgyn
ID: 36947423
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.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question