querying tables in a different database?

[Howard]

I use the 'source database' property of some of my queries to point them
at tables residing in a different database to where I need the answer -
a very useful technique. One snag with this though is when developing
the database at home I have to manually change the source database to a
different value to that used at work (and remember which queries need
changing).

All my linked tables get re-linked automatically via code and a button.

Is there a way to use code in a similar fashion to alter this property
of a query?

Howard

 

[Klatuu]

Yes, you can use the SQL property of a QueryDef
Load it, change it, and put it back.


Dim qdf As QueryDef
Dim strSQL As String

Set qdf = Currentdb.Querdefs("queryname")
strSQL = qdf.SQL
'
' make the changes to strSQL Here.

qdf.SQL = strSQL

Set qdf = Nothing

 

[Howard]

Yes, you can use the SQL property of a QueryDef
Load it, change it, and put it back.


Dim qdf As QueryDef
Dim strSQL As String

Set qdf = Currentdb.Querdefs("queryname")
strSQL = qdf.SQL
'
' make the changes to strSQL Here.

qdf.SQL = strSQL

Set qdf = Nothing

Thanks Klatuu, just what I needed!
Howard