Solved

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

Posted on 2011-09-22
8
276 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now