Password Change | Linked Table Manager (1 Viewer)

bparkinson

Registered User.
Local time
Today, 10:22
Joined
Nov 13, 2010
Messages
158
Environment:
Access 2010
SQL Server 2008 R2

We are on a shared SQL Server and therefore must use SQL Server authentication (I know, worst practice). I changed the password on the SQL Server. I changed the connection string in the Access client to use the new password. I reconfigured the ODBC DSN to use the new password, tested the connection and it works.The same SQL Server Management Studio, it works fine with the new connection string.

I ran Linked Table Manager, selected All, and after it ran, 9 tables and three views remained checked, and are unavailable - can't open them from Access.

Any Ideas? Thanks a lot in advance.

Bob
 

bparkinson

Registered User.
Local time
Today, 10:22
Joined
Nov 13, 2010
Messages
158
Problem solved. Deleted all the linked tables, re-linked them. Ran some VB code to remove the "dbo_" that Access prepends to linked SQL Server tables. All good now.
 

mdlueck

Sr. Application Developer
Local time
Today, 12:22
Joined
Jun 23, 2011
Messages
2,631
I use this code to update the ID/pw of Linked Tables:

Code:
'This API updates the ODBC connection settings to the remote SQLServer database
Public Function UpdateConnectionsBasedOnTypePassThrough(MSysObjectsConnectString As String) As Boolean
On Error GoTo Err_UpdateConnectionsBasedOnTypePassThrough

  Dim adoCat As Object
  Dim adoTbl As Object

  'Define attachment to FE database
  Set adoCat = CreateObject("ADOX.Catalog")
  Set adoCat.ActiveConnection = CurrentProject.Connection

  'Refresh Linked Tables
  For Each adoTbl In adoCat.Tables
    If adoTbl.Type = "PASS-THROUGH" Then
      If InStr(1, adoTbl.Name, "~", vbTextCompare) = 0 Then
        adoTbl.Properties("Jet OLEDB:Link Provider String") = MSysObjectsConnectString
      End If
    End If
  Next

  'Good return code
  UpdateConnectionsBasedOnTypePassThrough = True

Exit_UpdateConnectionsBasedOnTypePassThrough:
  'Clean up the connection to the database
  Set adoTbl = Nothing
  Set adoCat = Nothing

  Exit Function

Err_UpdateConnectionsBasedOnTypePassThrough:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: UpdateConnectionsBasedOnTypePassThrough(), adoTbl.Name: " & adoTbl.Name)
  UpdateConnectionsBasedOnTypePassThrough = False
  Resume Exit_UpdateConnectionsBasedOnTypePassThrough

End Function
To arrive at the syntax for the MSysObjectsConnectString I copied an existing string from the MSysObjects table. Then I merely updated the ID/pw within that string
 
Last edited:

jgier

Registered User.
Local time
Today, 09:22
Joined
Mar 19, 2012
Messages
21
@mdlueck Thanks for the quick reply, I will have to test this out.
 

kenlyle

New member
Local time
Today, 12:22
Joined
Jun 7, 2017
Messages
1
Just now, I found that updating the tables in the Access 2007 Linked Table Manager made my Recordsets start working again, no code required.
 

Users who are viewing this thread

Top Bottom