Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-22
8
Medium Priority
?
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1600 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 400 total points
ID: 36585358
Make sure you check the box 'Always prompt for new location' in the Linked tables manager.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 85
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 85
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

604 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