Can I run a query in another mdb?

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access 97

Is it possible to run a query in an mdb (unopened) from another opened mdb?
I guess remote instruction via some form of automation?

TIA

WSF
 
Yes, You can.

Set dbs = OpenDatabase("path")
Set rst = dbs.OpenRecordset("queryNameOrSQLStatement")
With rst
Do While Not .EOF
'enum values
Msgbox .Fields(1).Name
.MoveNext
Loop
End With
 
Thanks for that.
What I would like to do is run a maketable query in the separate mdb from my
control mdb.
When I try your example I get an error message
"Runtime error 3219 - Invalid operation"

??

WSF
 
Maketable queries don't generate recordsets.

Assuming that the Maketable query exists as a saved query, try:

Dim dbs As Database
Dim qdf As QueryDef

Set dbs = OpenDatabase("path")
Set qdf = dbs.QueryDefs("queryName")
qdf.Execute, dbFailOnError

If the query doesn't already exist, try:

Dim dbs As Database
Dim qdf As QueryDef

Set dbs = OpenDatabase("path")
Set qdf = dbs.CreateQueryDef("", "SQLStatement")
qdf.Execute, dbFailOnError

Make sure you have a reference set for DAO.
 
WSF said:
Thanks for that.
What I would like to do is run a maketable query in the separate mdb from my
control mdb.
When I try your example I get an error message
"Runtime error 3219 - Invalid operation"
Click to expand...

You probably have a references problem between DAO and ADO.

Another way to do what you want is to use an IN phrase in
the INTO clause of a local make table query.
 
Back
Top