ODBC: link from MS SQL Server to Access 2000, change of server

[Guest]

Hi!
I've inherited an Access database that is linked to an MS SQL Server database. Recently we got a new SQL Server and when installing it we renamed it.

So, how do I change the servername in Access 2000? I've changed the servername in the ODBC-connection but it doesn't help. I figure that there is a reference to the old servername somewhere in the Acess 2000 database but I can't find it.

I could really use some help here before I go crazy

Marina in Sweden

 

[jmonty]

Have you tried re-linking the table?
Since you've already updated the ODBC DSN, you are half
way there (I think).
Go to the Tools > Add-ins > Linked Table Manager.
When the dialog box pops up check the box that says
"Always prompt for a new location", then select the table
(s) in question. *** Be careful to only check the tables
that are linked to the old server, since it is possible to
have tables linked from different servers.
The ODBC DSN dialog should pop-up, then select the DSN
that you said you changed.(the new location) - then click
OK. You may have to know the sa password to link it, but I
think it should work.
Hope this is helps.
jmonty

-----Original Message-----
Hi!
I've inherited an Access database that is linked to an MS

SQL Server database. Recently we got a new SQL Server and
when installing it we renamed it.

So, how do I change the servername in Access 2000? I've

changed the servername in the ODBC-connection but it
doesn't help. I figure that there is a reference to the
old servername somewhere in the Acess 2000 database but I
can't find it.

 

[Guest]

Piece of cake when you learn how to do it!

*happy*

 

[Guest]

Hi!
I've inherited an Access database that is linked to an MS SQL Server database. Recently we got a new SQL Server and when installing it we renamed it.
So, how do I change the servername in Access 2000? I've changed the servername in the ODBC-connection but it doesn't help. I figure that there is a reference to the old servername somewhere in the Acess 2000 database but I can't find it.
I could really use some help here before I go crazy
Marina in Sweden

I just got though fighting the same problem. New servername, changed the dsn,
access ignores the dsn reference and uses the old servername. The problem is the
connection string for the linked table is more or less cast in stone. You have a
couple of choices. Either write the code to unlink and relink the tables ever
time the db is opened and closed. Or manually relink the tables. Or write the
code to change the tabledefs_
I complained about this but I'm told by the access experts that I an just one of
the ignorant unwashed.

I found this to be helpful:
http://members.rogers.com/douglas.j.steele/DSNLessLinks.html