?
Solved

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

Posted on 2011-09-22
8
Medium Priority
?
295 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

771 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