querydef: how to reference external odbc tables

[mikeg]

I can't seem to figure out how to write a query in ms-access(2007) that
points to data in an external odbc...i can do it on the fly in vba code, but
when i save it as a querydef i dont know how to pass along the
connectionstring with the rest of the SQL.
thanks!
Mike G

 

[Douglas J. Steele]

Dim qdfCurr As DAO.QueryDef
Dim strConnection As String
Dim strSQL As String

strSQL = "...."
strConnection = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"

Set qdfCurr = CurrentDb.CreateQuerydef("MyQuery", strSQL)
qdfCurr.Connect = strConnection