Create A Query (QueryDef) In Another Database

[JonS]

Hello,

I would a like a primary database to run a VBA Module procedure in a
secondary
database. That procedure creates a Querydef in that secondary
database.

When I run that procedure manually in that secondary database, the
procedure
works fine. That is, a Querydef appears in Queries window. However, I
can't seem to get it to work when I call the procedure from the
primary database.

I have placed information MsgBox's in that secondary procedure and so
when I call it from the primary database, I can see these MsgBox's
appearing, one of which details the SQL string for the Querydef. So it
all seems OK except that NO QUERYDEF IS PRODUCED when I go and look in
the secondary database. What am I doing wrong?

This is what I have done:-----

I have referenced the secondary database from the first.

I am using the following code from the primary database:

Dim A As Object
Set A = CreateObject("Access.Application")
A.Visible = True

A.OpenCurrentDatabase ("path and filename to secondary database")

'Call/Run the procedure in the secondary database

A.CloseCurrentDatabase
Set A = Nothing


In the secondary database procedure I use the standard code below to
make the Querydef:

Set qdf = dbs.CreateQueryDef(QueryName, strSQL)

As I have said above. If I run this secondary database procedure from
the secondary database, it works fine - but not when called from the
primary
database. Thanks,

JonS

 

[Allen Browne]

It's easy to create a query in another database if you use DAO:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = OpenDatabase("C:\MyPath\MyFile.mdb")
Set qdf = db.CreateQueryDef("MyQuery")
qdf.SQL = "SELECT * FROM MyTable;"

Set qdf = Nothing
db.close
Set db = Nothing