From one Access DB run queries in another Access DB

[ChrisOjeda]

I have having trouble running queries that are in an Access DB different from the one that I want to launch them from. I have part of my code that I believe works. I've tried some commands though they seem to try to run the queries from my current database (the one that the code is running from). The queries that I wish to run are Access queries.

If possible please show me the code commands assuming the three queries are "qry1", "qry2", and "qry3". Thanks... Chris



Private Sub Command5_Click()
Dim wksWorkspace As DAO.Workspace
Dim dbDatabase As DAO.Database

Set wksWorkspace = CreateWorkspace("Chris", "admin", "&quot
Set dbDatabase = wksWorkspace.OpenDatabase("T:\Default_Project\FC_Impact_Analysis\FC_Impact_Analysis.mdb&quot

dbDatabase.Close
wksWorkspace.Close

Set dbDatabase = Nothing
Set wksWorkspace = Nothing

End Sub

 

[ChrisOjeda]

Anyone got ideas?

 

[RickSpr]

I've occasionally looked for ways to do this in code, but never found one. One avenue I didn't pursue is using Automation. It's a way of "playing user" in your code to perform actions a user would be able to do through the user interface. As an example, when you select any of the items on the Tools|OfficeLinks menu, Access runs Word or Excel using Automation.

I haven't tested this, but it would go something like this:

    Dim appAuto As Access.Application

    On Error Resume Next
    Set appAuto = New Access.Application
    With appAuto
        .OpenCurrentDatabase(&quot;<file path>&quot;)
        .DoCmd.SetWarnings False
        .DoCmd.RunQuery &quot;qry1&quot;
        .DoCmd.RunQuery &quot;qry2&quot;
        .DoCmd.RunQuery &quot;qry3&quot;
        .DoCmd.SetWarnings True
        .CloseCurrentDatabase
    End With
    Set appAuto = Nothing

Note: The database opened using Automation remains invisible, unless you explicitly set its appAuto.Visible property to True. You might want to do that during testing, so you can watch for any errors.

There are many potential complications in using Automation, including such things as handling database passwords or user logon, and what happens if a runtime error occurs in the other database. But at least this is a start.

It'd be great if you get it working and post a follow up about any problems you solved in doing this. I've never seen anybody else discuss this technique on Tek-Tips, despite the fact that it has great potential value.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[cmmrfrds]

You can reference the access database directly in the select statement. Check these threads out.

thread705-567768
thread702-558955