Running a query from another database

Yes you can.  Your code in database B would be similar to this:

    Dim wsp As Workspace
    Dim db2 As DATABASE
    
    ' Return reference to default workspace.
    Set wsp = DBEngine.Workspaces(0)
    ' Return reference to Another.mdb.
    Set db2 = wsp.OpenDatabase("c:\FolderPath\databaseA.mdb")
    
    'To run a query in the other database
    db2.QueryDefs("NameOfQuery").Execute

    'Sample statement to execute a sql string in the other database
    db2.Execute ("Delete * From TableName")

Select allOpen in new window

Thanks...

Would you be able to explain each line in order to understand it.

Can this be done in A97 and/or A03

It can be run on either.  I added a comment before each line.  Which one in particular did you need help understanding?

hi,

thanks for your prompt rely...

but

how can apply this code to say A.mdb has the make table query but I also want to run that same query, independently from B.mdb

    Dim wsp As Workspace ******************don't understand?
    Dim B.mdb As DATABASE
   
    ' Return reference to default workspace.
    Set wsp = DBEngine.Workspaces(0) ************************ don't understand

    ' Return reference to Another.mdb.
    Set B.mdb = wsp.OpenDatabase("c:\FolderPath\A.mdb")
   
    'To run a query in the other database
    A.mdb.QueryDefs("NameOfQuery").Execute

    'Sample statement to execute a sql string in the other database
    B.mdb.Execute ("Delete * From TableName")

    'Sample statement to execute a sql string in the other database
    B.mdb.Execute ("Delete * From TableName") *************don't understand why delete???


Am I on the right  track?

The changes you made are incorrect:  I've highlighted the ones you actually need to change:

    Dim wsp As Workspace
    Dim db2 As DATABASE
   
    ' Return reference to default workspace.
    Set wsp = DBEngine.Workspaces(0)

    ' Return reference to Another.mdb.
    Set db2 = wsp.OpenDatabase("c:\FolderPath\A.mdb")
   
    'To run a query in the other database
    db2.QueryDefs("Name of query in A.mdb that you want to execute").Execute

    'I only included this Sample SQL statement to show how how you would execute a sql string in another database.  The Delete statement is only an example.  You can use other SQL statements (i.e create, insert, update, etc.).
    db2.Execute ("Delete * From TableName")

DBEngine.Workspaces(0) refers to the default workspace of the database you have open. A Workspace is akin to a session for the user and you can have multiple databases within that workspace.
 
One way to think about it is a Workbook in Excel that contains many Worksheets.  Not quite the same since you don't really see the Workspace or the other databases you opened in this workspace.

Assumptions:
Database1: A
database2: B
 B has make table query:mt
Both databases A and B reside on drive D:

A is running: code in cmdCheck click event to run mt in B

Private Sub cmdCheck_Click()
    Dim ap2 As Access.Application ' ap2 is an access application objects.
    Dim db1 As Database, db2 As Database  ' db1, and db2 are database objects.

    Set ap2 = CreateObject("Access.Application")

    Set db1 = Application.CurrentDb ' this is A
    ap2.OpenCurrentDatabase ("D:\B.accdb") ' opens B in background.
    Set db2 = ap2.CurrentDb ' this is B

    db2.Execute ("mt") ' runs make table query mt in database B
Exit Sub

Select allOpen in new window

@hnasr,
What is the purpose of line 7 since you aren't making use of db1?

Ron

@IrogSinta,
This is a copy and paste operation from an existing code. It is not used for this issue.

The purpose of db1 in original code is to compare between objects in both db1 and db2.

now I am completely lost?!!!???

You should not.
Try this sample. Save on drive D:, otherwise change drive letter in code.

Check code to remove messages and to modify the visibility of db_B.
db-A.accdb
db-B.accdb

I'm using A97 or A03 not A07

Did exactly what I wanted...many thanks

Welcome!