Update MS Access linked tables

masdf123
masdf123 used Ask the Experts™
on
Hello,

We have a access 2007 application.

There are some online table (SQL) which show up in the linked manager.

Now, the IP address of the SQL server has changed. Can you please guide how to update the tables showing up in the linked table manager.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Has sthe SQL Server service name changed, too?

Author

Commented:
no, just the ip address.
If you've linked via ODBC, you may need to go in to the ODBC setup in Windows and adjust the connector string to the new server path. Then you can close and reopen the DB. If it doesn't reconnect you may have to refresh the links:

Right-click on a linked table in the left window, select "Linked Table Manager" from the popup menu, select the tables to refresh, and hit OK.

-Rachel
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Could please give me little more detail on adjusting the connector?
When you hold your mouse over a linked table without clicking on it, you should see a popup of the connection string it's linked with. It'll read something like:

"ODBC;DSN=<DSN Name>;UID=..." and go on from there

You care about the DSN Name value (there may be more than one amongst the tables if you're linked to multiple databases, but only one per table)

In your Windows Control Panel, go to Administrative Tools, then to Data Sources(ODBC).

Under either User DSN or System DSN, depending on how it was initially set up, there will be one or more Data Sources...find the one that matches your DSN name and double click on it. Assuming you haven't been blocked on the rights on your machine by an administrator, you can then step through and adjust it. Your "Server" field will reflect the old IP address. You can either put in the new IP and continue through the steps thereafter - don't change settings unless your network admin says you should. Depending on existing settings, you may need to enter a special password associated with the account making the link (if it's not just using your Windows login password).

-Rachel
1.  --------------------------------------
From the computer with MS Access make sure that you can connect to you 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.

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