How do I change the SQL server for Access ADP?

[rcmail14872]

I have MS SQL Server 2000 evaluation version on my desktop PC. I have
the SQL Server client tools (only) on the laptop. The two computers are
networked. I had an Access MDB database on my laptop, but I converted
it with the Access upsizing wizard to an Access ADP database with the
Access front-end on the laptop connecting to the SQL Server on the
desktop. Now I want to move from this test environment to the "real"
server. How do I go about this? Do I make the change from the laptop or
the desktop? Do I use the Access drop-down menu, Tools > Database
Utilities > Transfer Database? Or maybe from the desktop with: SQL
Server Enterprise Manager > Copy Database Wizard?

 

[Sylvain Lafontaine]

They are two easy ways: make a backup/restore of your database or detach
your database, copy the file and reattach the database to the new server.
Before proceeding, it's always a good idea to make that the latest service
packs are installed on both server.

The Copy Database Wizard can be usefull if you don't have an easy way to
copy files between the two servers; however it has often a very bad time
with databases whose schemas are anything but very simple. In the past, I
never been able to use it successfully and I've always lost my time with it.

I don't know about Access' Transfert Database utility.

S. L.

 

[rcmail14872]

Thanks, I will try to make a backup and I assume that will give me a
single file or a set of files which I can then just copy to the new
server and then change the connection string in the ADP file to point
to the new server. I assume that the way to do the backup is through
the backup feature of SQL Server Enterprise Manager.