Can I run a query in another mdb?

[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

 

[losmac]

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

 

[WSF]

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

 

[Douglas J. Steele]

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.

 

[Marshall Barton]

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"

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.