Sub to Auto-refresh Connect string for all pass through queries

[richardb]

I think I'm pretty close on this one. I want to automatically update the
"connect" string of all the pass through queries in my database. This will
make the system more portable (work on it at home, deliver to customer and
visa versa). I just can't find how to apply the strConn to the query object
(see place where I think it should go):

Public Sub RefreshQPassConnections()
On Error GoTo RefreshQPass_Err
Dim obj As AccessObject, dbs As Object
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 = Application.CurrentData
' Search for pass through queries in AllQueries collection.
For Each obj In dbs.AllQueries
If Left$(obj.Name, 5) = "qpass" Then
obj.Connect = strConn (I DON'T THINK THIS IS CORRECT!!)End If
Next obj


RefreshQPass_End:
strConn = vbNullString: strDescription = vbNullString: strDSN =
vbNullString
Set obj = Nothing: Set dbs = Nothing
Exit Function

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

 

[richardb]

I know that the exit string says Exit Function instead of Exit Sub.