Programmtically set Source Database value in Query Properties

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know how to set the Source Database value (under a queries
properties) in an existing query using visual basic?

Thank you,

Sarah
 
Sarah,

I'm not sure that I understand your question. You can only use a table or
another query within a query. The tables can be located in the current mdb
file or they can be in an external mdb file. If you want to use a table from
an external database file then you need to use the File, Get Externl Data,
Link Tables ... option to link the tables.

Once the tables are linked, they can be use just like a local table.
 
I know that and that's not what I'm asking for. I want to be able to prompt
a user for an external database file which will contain the same type of
tables used in the existing database. I want to be able to duplicate
information from this database file into the current database. In order to
do this I'm using append queries which need there source database value set
to whatever the user selects for the external database. There fore I need to
know how to set this value programatically.
 
You'd have to rewrite the SQL to use the IN clause, like

SELECT CustomerID
FROM Customers
IN OtherDB.mdb
WHERE CustomerID Like "A*";

It's fairly straightforward to change the SQL associated with a query.

Dim qdfCurr As DAO.QueryDef

Set qdfCurr = CurrentDb().QueryDefs("MyQuery")
qdfCurr.SQL = strSQL

This assumes your external database is another Access file.

If you've got pass-through query that's going against some ODBC-compliant
DBMS, you can simply change the Connect property of the QueryDef object.
 
Back
Top