We help IT Professionals succeed at work.

Change the password for linked tables in access

Ross_G
Ross_G used Ask the Experts™
on
I have a pile of access front ends that have been written to access an SQL back end via linked tables. The DB has been moved from one server to another, in another country!!, and the password for the linked tables is no longer valid. the ODBC DSN has been set up and works if you manually enter the new password in Link Manager to refresh the tables, but I need to change the password that the access db was given for the SQL when they were built. Any ideas on how we can do this?? I have trawled through Access and Google and have had no luck in finding this.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Access 2003 or 2007?

Author

Commented:
Was written in 2003, but have 2007 to access if necessary.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Right-click on any linked table, choose "Linked Table Manager"
Select all the tables (Select All), then tick "Always prompt for location"

When prompted, select the new ODBC dsn, and provide a password if not using Windows Authentication.
So as not to be confused, it may be easier just creating a new ODBC DSN beforehand.

Author

Commented:
Tried this. Created a new DSN. LInked fine, but asks for the password each time you start up. If you flag the 'trusted connection', you get the ODBC errors back. This access need to run in the background and be invisible to the user..
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
The UI for 2003 and even 2007 do not show the "Save Password" box in a re-link operation.
You can re-link if the ODBC connection uses Trusted connections, but if not, then you need to either use VBA macro, or delete and relink all tables.

Here's VBA code you can use.  In the line .Connect = "ODBC....
update all the parameters to your actual values.
Your current connection string is visible from

Choose linked table, right-click, Design View.  (answer prompt = yes)
From menu, View->Properties.
The first item "Description" contains the current connection string (including password if any).
Sub relinkme()
'Returns all linked tables
    Dim tdf As TableDef, db As Database
    Set db = CurrentDb
    db.TableDefs.Refresh
    For Each tdf In db.TableDefs
        With tdf
            If Len(.Connect) > 0 Then
                If Left$(.Connect, 4) = "ODBC" Then
                '    collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
                'ODBC Reconnect handled separately
                    .Connect = "ODBC;DRIVER=SQL Native Client;SERVER=sqlsvr;DATABASE=mydb;UID=newuser;PWD=newpass;APP=Microsoft Office 2003;TABLE=INFORMATION_SCHEMA.COLUMNS"
                    .RefreshLink
                End If
            End If
        End With
    Next
    Set tdf = Nothing
    Set db = Nothing
End Sub

Open in new window

Author

Commented:
Thanks - all working correctly now. Appreciate your quick response!!