Hi,
I want to run a query, but this query is not in my current database, it is in another database. How do I run it?
I try to use Docmd.runquery, but I do not know how to write the rest of the code.
Anybody can help me?
thanks
cindy

Use ADO.



Sal

Why don't you bring the query into your current database?

I am a loud man with a very large hat. This means I am in charge

It's relatively simple with DAO:

Dim db As DAO.database
Set db = opendatabase("C:\Path\to\File\file.mdb")
db.Execute "query1"
db.close
set db = nothing

You need a reference to the DAO library (code window, Tools menu - References), a path to your database with file name and a saved query name.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Thank you! I will try it today.
cindy

Hi, jurgenw and all:
I tried DAO. But my query(qryGet for example) is a make table query(make a table named tblGet for example), so if I've never run this query before, it is ok. But if I've run the qryGet before, that means the table tblGet has data in it, it will give the error message, it said: tblGet table already exist."
thanks
cindy

I would suggest running a delete query to delete the records and then run an append query to populate the table if it always has the same structure. Just run the queries in order by adding db.Execute lines for each action query. Alternatively you can delete the table using DAO before you run the make table.

db.TableDefs.Delete ("tblGet")

You will need to add an error handler to handle the case where the table doesn't exist.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Either that or use Execute, such as:
All nicely done with SQL, no ADO/DAO thoughts needed



I am a loud man with a very large hat. This means I am in charge
Edited due to typo

To make it totally clear - YourNewTable is the one you are creating, and YourOtherTable is the source table

I am a loud man with a very large hat. This means I am in charge

Using SQL is viable but calling CurrentDB twice, after all it is a function, calls a refresh of all the local database collections. I would strongly suggest that an object variable be used so you need only call CurrentDB (or opendatabase) a single time as I do not believe that it should be necessary to refresh local collections even though a table has been dropped, especially when it is in an external database.

Setting a reference via:

  Workspaces(0).Databases(0)

or

  Workspaces(0)(0)

instead of calling CurrentDb works with the most recently refreshed version which may be as old as the time the database was last opened and avoids even this overhead. Using CurrentDb one time to set an object variable is a useful and reasonable compromise though completely unnecessary when running SQL in an external file. In addition, opening an explict reference to the database being operated on, even via SQL, will keep the connection open saving the time to renegotiate the lock file for the external file. This can be quite significant if other users have the target file open. This can yield significant performance benefits when using SQL to reference an external mdb file with "in 'YourDB.mdb'" syntax.

I would recommend the 'OpenDatabase' method I suggested at it is simple, direct (fewer words) and trivial to set up a meaningful error handler for. And in any event, using CurrentDb to run the SQL requires DAO so DAO thoughts remain needed.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]