Mass update of Pass-Through Query ODBC settings?

  • Thread starter Thread starter zoranlee
  • Start date Start date
Z

zoranlee

I'm more of a "server side" guy but I have to deal with an Access
front end on a client site now.

I wanted to move the whole thing to my laptop as it's a small DB (both
SQL Server and Access).

I figured out how to use Linked table manager for tables to point to
my local SQL DB but I see not way of doing something similar for all
those pass-through queries in Access.

Do I have to go into ODBC Connect Str of each and every Query and
manually change the strings every time I need to change the location
or there is there an easier way?

Thanks

Z
 
No, you don't have to do this manually.

Assuming you have a reference set to DAO, you can use something like the
following untested air-code:

Sub ChangeConnections(NewConnection As String)
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
If Len(qdfCurr.Connect) > 0 Then
qdfCurr.Connect = NewConnection
End If
Next qdfCurr

Set dbCurr = Nothing

End Sub

This assumes that all of your pass-through queries have the exact same
connection string.
 
Back
Top