Editing Linked Table Information In Access 2003

1
votes

I have an Access 2003 database with linked tables to a SQL Server 2005 database. The user information (the password) that is used to create an ODBC connection between Access and SQL Server was recently updated.

When I open the Access database, and try to edit the Linked table information I am then able to open the tables and see my data. However, when I close Access and and reopen the Access database it appears the password informtion has revereted back and I get an ODBC connection error.

Anyone know what I am doing incorrectly?

As a follow up, it appears we have about a dozen Access databases with numerous linked tables that all need this update. Is this the best way to update this information? The linked tables seem to have been created using different machines as the Workstation-ID specified in the ODBC connection is different.

2
ms-accessodbc
Have you looked at the linked table manager? There should be a "save password" checkbox. If it is unavailable, see support.microsoft.com/?id=207823 - Fionnuala
@Reemou, good idea, but problem with linked tables that are linked to a view. - Patrick Honorez
If you don't have many tables, delete them and reattach them after creating the proper DSN. - Patrick Honorez
Is there a reason why you're using SQL Server authentication instead of Windows authentication? It's much, much easier in this regard to use the latter -- your Access application will no longer need to care about the user authentication issues at all. - David-W-Fenton

2 Answers

0
votes

Write a routine, that update the Connect Property from the TableDef and save the change with RefreshLink.

0
votes

The problem with Linked Table Manager (LTM), is when you have linked tables that are in fact links to SQL Views. In that case, LTM will relink the "tables" without reassigning them the proper PK, and they will become non updatable. I have written some code that I used to start from VBE, it is a quick and dirty thing, but you could surely adapt that if you need it. There are 2 subs, one for the tables, and one for the passthru queries.

Option Compare Database
option explicit

Const kOld = "remote.g"  'string to identify old server
'new server odbc string
Const kConnLux = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=xxxx;UID=yyyy;PWD=zzzz;"

Sub UpdateTables()
    Dim db As Database, td As TableDef
    Dim hasIndex As Boolean, strSql As String

    Set db = CurrentDb
    For Each td In db.TableDefs
        If InStr(1, td.Connect, kOld) > 0 Then  'lien vers CE serveur ?
            If td.Name Like "dbo_vw*" And td.Indexes.count = 1 Then 'table = vue attachee --> pbl de clef primaire
                strSql = "CREATE INDEX " & td.Indexes(0).Name & " ON [" & td.Name & "](" & td.Indexes(0).Fields & ")"
                ' convert field list from (+fld1;+fld2) to (fld1,fld2)
                strSql = Replace(strSql, "+", "")
                strSql = Replace(strSql, ";", ",")
                hasIndex = True
            Else
                hasIndex = False
            End If
            td.Connect = kConnLux
            td.RefreshLink
            Debug.Print td.Name
            If hasIndex And td.Indexes.count = 0 Then
                ' if index now removed then re-create it
                CurrentDb.Execute strSql
            End If
        End If
    Next td
    Debug.Print "Done"
End Sub

Sub UpdateQueries()
    Dim db As Database
    Dim td As QueryDef
    Set db = CurrentDb
    For Each td In db.QueryDefs
        If InStr(1, td.Connect, kOld) > 0 Then
            td.Connect = kConnLux
            Debug.Print td.Name, td.Connect
        End If
    Next td
End Sub