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.