Update connect string in queries.

[richardb]

I am having trouble creating a sub routine to read through all of the "pass
through" queries (having names beginning with 'qpass') in my Access database
and update the connect string. Please see my post of 02/14/2006 "Sub to
Auto-refresh, etc." I would appreciate a correction to the code of that post
or a code example or reference. Thank you.

 

[Douglas J. Steele]

I think this is a case where you need to use DAO.

Public Sub RefreshQPassConnections()
On Error GoTo RefreshQPass_Err
Dim obj As DAO.QueryDef, dbs As DAO.Database
Dim strConn As String, strDSN As String, strDescription As String
strDSN = varLookup("Lookup", "tlkpStoredStrings", "VariableName =
'DSN'")
strDescription = varLookup("Description", "tlkpStoredStrings",
"VariableName = 'DSN'")
strConn = "ODBC;" & strDSN & ";Description=" & strDescription & _
";Network=DBMSSOCN;Trusted_Connection=Yes"
Set dbs = CurrentDB()
' Search for pass through queries in the QueryDefs collection.
For Each obj In dbs.QueryDefs
If Left$(obj.Name, 5) = "qpass" Then
obj.Connect = strConn
End If
Next obj

RefreshQPass_End:
Set obj = Nothing: Set dbs = Nothing
Exit Function

RefreshQPass_Err:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Referesh
QPass Connect"
Resume RefreshQPass_End
End Sub

By the way, are you aware of the fact that you can do this without having to
create a DSN?

Get rid of strDSN and strDescription, and set strConn to

strConn = "ODBC;Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=MyDatabaseName;" & _
"Trusted_Connection=yes"

Reploace MyServerName and MyDatabaseName with the appropriate values.

 

[richardb]

Pefect and thank you very much. I'll try to learn more about DAO. This is not
the first time you have helped me and I appreciate it. Richard

 

[richardb]

Doug,

I just re-opened your reply and saw the last part. No I didn't know that,
but will use it. Thanks again.