Moving SQL server for an Access Application

[MJ]

Some time ago I created an Access application by using the Upsizing Wizard
so as to place the data on an SQL server. I will soon be upgrading the
server to a new model with a new SQL server name and I would like to know
how to change the Access application to point to the new data source.

Thanks in advance

MJ

 

[MJ]

I am running Access 2002 and I don't seem to have a connections option in
the file menu.

Does this mean that what I have is not an ADP?

I cannot work out how to change the server name.

MJ

 

[BJ Freeman]

open up the VBA editor
(ALT F11)
open up the intermediate window

?currentproject.BaseConnectionString
will tell you how you are connected now.

 

[MJ]

The file is an MDB. I have tried the following:

Take the current SQL database offline
Open the MDB and select the linked table manager
Select a table and click OK
I then get a Connection Failed SQLState 08004 SQL Server Error 4060 error
Click OK and I then get an SQL server login window.
I select the new server and click OK.
I get a message telling me that All selected linked tables were refreshed.
If I then double click the linked table I get an error that the connection
to the original server has failed.

What I would really like is to simply link the tables via an ODBC source
that I define via the control panel.

MJ

 

[BJ Freeman]

glad to see I am not the only one that mis types URLs. had to add the L at
the end.

http://members.rogers.com/douglas.j.steele/DSNLessLinks.html