SQL Server - Connection String Problem after changing name of Server

Declan_Basile
Declan_Basile used Ask the Experts™
on
I'm running SQL Server Express 2008 on a Windows Server 2008 Server.  I changed the name of the server computer from "BackupServer" to "AltekNT".  I can connect to the instance of SQLServer running on the server from a Windows XP computer using Management Studio and using the name "AltekNT\SQLEXPRESS" (former name was "BackupServer\SQLEXPRESS").  However, the connection string in my Access Database Program will not connect any tables using the name "AltekNT\SQLEXPRESS" (Note: Using "BackupServer\SQLEXPRESS" in the connection string before I changed the name of the server worked to create linked tables).  How can I fix this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Security Analyst Senior
Commented:
Couple of things you can do..

There used to be a global Search and Replace for Access..

http://www.rickworld.com/products.html

The other thing you can do is modify your local hosts file to use the old name with the new server's IP until you get the code sorted out..

HTH,

Kent
Top Expert 2012
Commented:
>> How can I fix this? <<
You need to change the name of SQL Server instance to AltekNT.  Here is how you do it:
How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server
http://msdn.microsoft.com/en-us/library/ms143799.aspx
fomand:
1.  --------------------------------------
From the computer with MS Access make sure that you can connect to your new MSSQL instance.

Sometimes during MSSQL move some permissions could be lost - they are not included in backup/restore procedure.

Use SSMS to verify it. If you cannot access new instance of MSSQL from SSMS under MS Access acount don't touch MS Access.
You must (or ask MSSQL admin) gran access rights to MS Access 2007 account that was used in the link on new MSSQL.

It could be Windows authentication - the user in this case Domain/WinUser.
It could be MSSQL login - the login must be created on new instance of MSSQL. Access rights must be granted to it.

Try MS Access now (the best way is to reboot your computer with MS Access before the test. MS Access can keep old settings until reboot. Maybe reboot is too much but it refreshes everything).

2 -------------------------
If you still have the issue:

If "connection from SSMS" / "MS Access 2007 account rights" is okey then adjust the link of ODBC as RemRemRem suggested. Maybe server name was changed.

In ODBC manager there is "Test Connection" function.
It must tell ~"Connection successful".

Try MS Access now.
 
3. ------------------------
If you still have the issue:
 
1. create test MS Access DB.
2. Create a test table.
3. Link the test table to MSSQL table.
4. Select * from test table or put it on test form.

You must make it working before you touch your main MS Access 2007 database.

Try MS Access now.

4. -------------------------
If you still have the issue: test DB works and main DB does not:
Use MS Access Link Manager to re-link MS Access tables.
Aaron ShiloChief Database Architect
Commented:
hi

what you shoud do is

sp_dropserver <'old_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO

Open in new window


read more
http://msdn.microsoft.com/en-us/library/ms143799.aspx

Author

Commented:
Thanks for all the info.  Eventually the routines to create the linked tables in Access worked without changing anything.  I'm not sure why.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial