Pragmatically setting DSN'less connection for a pass-through Query object?

[mdlueck]

I started researching the original problem over here...

"Multiple LEFT JOIN query fighting SQL Server -> Access port"
http://www.access-programmers.co.uk/forums/showthread.php?p=1097853

I have wound around to thinking I must use two Access query objects, one of which in Pass-Through mode. I am fine with that.

To support Linked Tables in the FE DB, I developed some code to update the DSN'less connection settings for those linked tables each time the application starts. Thus I thought to also update the query objects in the FE DB. Code snippet of what I have working already...

  Dim adoCat As New ADOX.Catalog
  Dim adoTbl As New ADOX.Table

  'Define attachment to FE database
  Set adoCat = New ADOX.Catalog
  Set adoCat.ActiveConnection = CurrentProject.Connection
  Set adoTbl.ParentCatalog = adoCat

  'Refresh Linked Tables
  For Each adoTbl In adoCat.Tables
    If adoTbl.Type = "PASS-THROUGH" Then
      adoTbl.Properties("Jet OLEDB:Link Provider String") = ThisConnectString
    End If
  Next

Road Block! There does not comprable objects existing to update the part of the ADOX object where the queries reside. I elaborated this point in the prior thread I posted a link to above.

Suggestions please at how to arrive at query objects which will not prompt for connection settings, just link Linked Tables do not prompt for connection settings?

 

[mdlueck]

It would seem that it is required to use DAO objects to update Query objects. (Rather than ADODB) Thus my Rx snipped as follows:

  Dim daoDB As DAO.Database
  Dim daoQDF As QueryDef

  'Update the pass-through query objects
  Set daoDB = CurrentDb()
  For Each daoQDF In daoDB.QueryDefs
    '112 seems to be the flag that the query is a PT query
    If daoQDF.Type = 112 Then
      daoQDF.Connect = ODBCConnectString
    End If
  Next

Next up, I am considering moving the query objects into DAO VBA code... less things for attracting tinkering.