Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 05-16-2012, 07:25 AM   #1
bparkinson
Newly Registered User
 
Join Date: Nov 2010
Posts: 158
Thanks: 1
Thanked 19 Times in 19 Posts
bparkinson is on a distinguished road
Password Change | Linked Table Manager

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 is offline   Reply With Quote
Old 05-16-2012, 06:23 PM   #2
bparkinson
Newly Registered User
 
Join Date: Nov 2010
Posts: 158
Thanks: 1
Thanked 19 Times in 19 Posts
bparkinson is on a distinguished road
Re: Password Change | Linked Table Manager

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.
bparkinson is offline   Reply With Quote
Old 05-17-2012, 04:39 AM   #3
mdlueck
Sr. Application Developer
 
Join Date: Jun 2011
Posts: 2,584
Thanks: 110
Thanked 294 Times in 282 Posts
mdlueck will become famous soon enough
Re: Password Change | Linked Table Manager

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

__________________
--
Michael Lueck
Lueck Data Systems

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by mdlueck; 02-28-2013 at 10:08 AM. Reason: Update to convert to Late Binding coding style so that the ADOX reference is not needed
mdlueck is offline   Reply With Quote
The Following User Says Thank You to mdlueck For This Useful Post:
jgier (08-02-2012)
Old 08-02-2012, 12:15 PM   #4
jgier
Newly Registered User
 
Join Date: Mar 2012
Posts: 21
Thanks: 13
Thanked 0 Times in 0 Posts
jgier is on a distinguished road
Re: Password Change | Linked Table Manager

@mdlueck Thanks for the quick reply, I will have to test this out.
jgier is offline   Reply With Quote
Old 06-06-2017, 03:20 PM   #5
kenlyle
Newly Registered User
 
Join Date: Jun 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
kenlyle is on a distinguished road
Re: Password Change | Linked Table Manager

Just now, I found that updating the tables in the Access 2007 Linked Table Manager made my Recordsets start working again, no code required.

kenlyle is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linked Table Manager PaulO General 6 04-23-2009 01:49 AM
Linked table manager ideefixe Modules & VBA 5 11-10-2006 09:35 AM
Linked Table Manager pansraohk General 2 09-15-2004 05:57 PM
[SOLVED] Linked Table Manager ashaaya General 1 05-29-2003 03:21 PM
[SOLVED] Linked Table Manager Erika General 3 03-12-2003 11:22 AM




All times are GMT -8. The time now is 10:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World