I have a linked table db (DAO) in which I am using pass-through queries. When the db is moved from local to development and then to test and production, I need to point it to the right server by manually changing the connection string in VBA code (I don't store the connection string in the queries' "ODBC Connect Str" property not to let advanced users see the connection string.
Is there a way to automatically redirect these queries based on the connection string of the db itself? Something like:
qdf.connect=currentdb().connection so that the qdf could automatically be run against the server the tables are linked to?
The CurrentDb is a DAO Jet database while the Connection property is only accessible with a database in an ODBCDirect workspace. You cannot reference this property for the CurrentDb object (Run-time error 3251: Operation is not supported for this type of object.). The Connect property of a Database object is a zero-lenght string ("") when its base tables are in a Jet database (.mdb), which is the case of the CurrentDb object.
You can retrieve the elements of the connection string from the Connect property of a linked table (by the way, your defence against advanced users is useless: what they cannot see from a pass-through query, they can from a linked table).
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Tbl_LCF_Data")
Set qdf = dbs.CreateQueryDef
' Retrieve connection from a linked table.
qdf.Connect = tdf.Connect
qdf.SQL = "SELECT * FROM CF_Data"
qdf.Name = "Qry_CF_Data"
You can also create a custom property on the CurrentDb object and use it to store the connection string for the queries.