Performing "save password" on linked tables in VBA

[pjdavey]

Hi all,
Anyone know if there's any way of simulating the
"save password" feature (in the link tables dialogue)
from VBA.
(I have a db with hundreds of linked tables, and have
changed the odbc dsn (from 16 bit access 2.0 to 32 bit
access 97), and have successfully relinked the tables
but now i have to supply the sql server password whenever
i need to access a table.)

Thanks.

 

[tman138]

Hi PJ. I have acouple of functions that will do just that for you. To understand what is happening, go to tools, options and on the general tab check the 'Show System Objects' box. Then from the database window select the MSysObjects table. Filter for non nulls in the 'Connect' column. This will show all of your system objects with connection strings. The following subs and function work together to loop through this table parsing the connection strings and replacing the old DSN with the new one. Create a module and paste this code:

Sub subChangeConnection(strFrom As String, strTo As String)

Dim ws As Workspace
Dim db As Database
Dim tbl As TableDef
Dim intI As Integer
Dim strConnect As String
Dim strTest As String

Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)

For intI = 0 To db.TableDefs.count - 1
Set tbl = db.TableDefs(intI)
If Left$(tbl.Connect, 4) = "ODBC" Then
strTest = tbl.name
strConnect = fnChangeConnectString(CStr(tbl.Connect), strFrom, strTo)
If tbl.Connect <> strConnect Then
tbl.Connect = strConnect
tbl.RefreshLink
End If
End If
Next intI

End Sub


These functions change the connection string from DSN ABC to DSN XYZ and vice versa. You of course will have to substitute your from and to DSN's

Function fnChangeAbcToXyz()
Call subChangeConnection(&quot;ABC&quot;, &quot;XYZ&quot
End Function

Function fnChangeXyzToAbc()
Call subChangeConnection(&quot;XYZ&quot;, &quot;ABC&quot
End Function

I routinely use this to test applications by switching between my Oracle production and test databases.

Hope this helps....T

 

[pjdavey]

Thanks, i'll give it a go !