Running queries from an outside database

[Rog]

How do I run queries in one database from an outside database?

I've got lots of databases, each with lots of queries, that I have to run over and over. I've created a routine which runs all of the queries in one database and does other good things. (I haven't succeeded in getting it to work as an add-in, but that's no problem - I just import the routine into each database.)

I now want to create a 'super-query runner' - which will run all of the queries in all of my databases in one go...

So in database 1 I have the super query runner, and in databases 2, 3, 4 etc I have tables with associated queries.

I get to run the queries ok, but the queries can't find the tables that they should run on.

The code looks like this...
Dim ws As Workspace
Dim db As Database
Dim qry As QueryDef


' Create Microsoft Jet Workspace object.
Set ws = CreateWorkspace("", "admin", "", dbUseJet)

' Open Database object from saved Microsoft Jet database
' for exclusive use.
Set db = ws.OpenDatabase("D:\My Documents\Not Monteray\Access And VBA\Temp.mdb", True)

' Set db = DBEngine.Workspaces(0).Databases(0)
Debug.Print "Workspace "; ws.Name; " Database "; db.Name

' ******************************************************************

For Each qry In db.QueryDefs
If Not (qry.Name Like "~*") Then
Me.txtQryName = qry.Name
Me.txtQryContent = qry.SQL
DoCmd.OpenQuery qry.Name
End If
Next qry

 

[Fornatian]

Can you not have linked tables in your SuperQueryRunner DB?

Alternatively take a look at the Source Connect Str help(you can access this by right clicking the grey area of the QBE window and viewing properties, you should then be able to reference tables outside the currentDB. I've not used it so I can't provide a detailed answer but it looks capable of answering the question.

Ian

Ian

 

[Rog]

Just got back to this. Thanks, Ian. I didn't have my tables linked. I'll try it today

 

[Randomblink]

Another thing I noticed that may actually have NOTHING to do with it is the naming convention...

I noticed that you have spaces in the names of the folders and files...etc...

Spaces can throw things around, if, for instance, you are in Win NT...

Just a thought...