running a make table query from another database

[Paul Ponzelli]

Is there a way to run a make table query in an Access 2002 database from a
VBA procedure in another Access 2002 database?

For reasons related to performance, I sometimes import data from external
Access databases into other Access databases instead of linking the tables,
but I need to make sure that some make table queries are run on a regular
basis in those mdb files from which the tables are imported.

To keep the data current at least on a daily basis, I'd like to run these
queries from a control database in the early morning hours when no one has
the files open. Is there any VBA code I can use to run an action query in
one Access database from another Access database?

Thanks in advance,

Paul

 

[Rob Oldfield]

Dim db As Database
Set db = OpenDatabase("<path>\RemoteDB.mdb")
db.Execute ("YourQuery")
Set db = Nothing

 

[Paul Ponzelli]

Dim db As Database
Set db = OpenDatabase("<path>\RemoteDB.mdb")
db.Execute ("YourQuery")
Set db = Nothing
Thanks, Rob - it works great. A related question:

I looked up the Execute method in Access Help, and it appears you can only
run a query with it. Is there a way to generalize this method to run ANY
Sub or Function in another database?

Thanks again in advance,

Paul

 

[Rob Oldfield]

Dim db As Database
Set db = OpenDatabase("<path>\RemoteDB.mdb")
db.Execute ("YourQuery")
Set db = Nothing

Thanks, Rob - it works great. A related question:

I looked up the Execute method in Access Help, and it appears you can only
run a query with it. Is there a way to generalize this method to run ANY
Sub or Function in another database?

Thanks again in advance,

Paul

As long as the function is public, then you can just add a reference in to
the calling db and run it via dbName.FunctionName

 

[Paul Ponzelli]

Does "add a reference in to the calling db" mean

Dim db As DAO.Database
Set db = OpenDatabase("<path>\RemoteDB.mdb")

?

 

[Paul Ponzelli]

Still another related question:

If the database in which you run a query or call a procedure produces a
messagebox error, will you see it in the calling database, so you can click
it away, or does it sit there in the "called" database until someone opens
it?

 

[Rob Oldfield]

No. It means go to a code window, use Tools, References, and then browse to
the target db.

 

[Rob Oldfield]

I don't know. I've never used the technique in a production db. Get the
reference thing working as per my other answer and you should be able to
answer this one for yourself. <g>

 

[Paul Ponzelli]

Great. Thanks for talking me through this, Rob. This is going to be a big
help to us.

 

[Paul Ponzelli]

Get the

reference thing working as per my other answer and you should be able to
answer this one for yourself. <g>

BUSTED!!! <g>

Ok, you're right, I can check it for myself.