Run Query From Separate Database

[bigmerf]

I have two databases that store two different types of information. Both databases are rather large and keep track of different types sales and items. I would like to create a "sales log" that will show total sales volume from "Database A" and import the totals from "Database B" to incorporate those totals to give an OVERALL GRAND TOTAL. I have created a Make Table option within "Database B" so that the pertinent information will be dumped into a blank table. From there I can then use the "Import" function or even the link table feature from "Database A" if I need to.

Now, what I'm curious about is if there is a way to run a query from another database when you have a totally different database open. In this example, I have "Database A" open but would like to run a query located within "Database B". All security rights and privileges exist so there is no problems accessing the other database.

Any ideas?

 

[JerryKlmns]

Why not link the table you want to the other database, or link both tables to a third database, and create your desired queries there? It would take longer to execute any query based on linked table rahter on local and especially on network drive, but you wont have to export-import-compact etc...

Also, to combine 2 tables/queries to be as one, take a look at
Union Queries, saves you the append operation.

 

[sxschech]

You can also run queries from the other database using the "IN" statement.

SELECT * FROM TABLE IN "C:\OtherDatabase.mdb";

 

[bigmerf]

So how would the code look then? Would I create a query of my own using the above SQL code? How would I write that logic?

SELECT "query.name"
FROM ???????
IN "C:\OtherDB.mdb"

????

 

[markphsd]

If you are using VBA.

Add a New module:

Declare a private connection object:

Private moMyCon as adodb.connection

Add a function to instantiate and use he object:

Public function GetMyCon() as adodb.connection

If moMyCon Is Nothing Then
sCon = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & myPathtoMyDb & ";"
Set moMyCon = New ADODB.Connection
moMyCon .Open sCon
Set GetMyCon = moMyCon
Else
Set GetMyCon = moMyCon
End If

end function


Then use the connection object in your project:

sSQL = "SELECT * FROM myQuery"
rst.open sSQL, GetMyCon

etc..


Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[sxschech]

You would write the query as any other query, the only difference is that you would add the in statement. If you prefer to use the graphical interface instead of the SQL interface, then before adding any tables, bring up the properties window and in the Source Database property, change (current) to the location of your outside database. Note that if you do it in the properties, there is no popup wizard, so you would have to spell the name of the database correctly or copy and paste the name in. Once the Source Database property is set to the external database, then you can use the graphical add table button to select tables or queries from the external database. From that query, you can then join or union it with your local table/query to get results you need if you need to combine data from both databases. If you need further clarification, let me know.

 

[sxschech]

As a follow-up, you can also create the make table query in the database you prefer to run in, and it can perform the make table on the external database. When you set up the make table query a popup will give the the choice of running it in the current database or another database.

 

[bigmerf]

That's exactly what I was looking for. I was able to create a new query and change the properties so that the source database points to the "other" database. It's working great.

Thanks for eveyone's help!

 

[IamCornholio]

What about if you're trying to execute an action query in another db. For example.


Private Sub cmdRUN_click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = OpenDatabase("\\UNC_PATH\2ndAccess.mdb", False, False, "MS Access;PWD=mypassword")


now I want to call an action query. In the current db I would write

DoCmd.OpenQuery "qryAPP_Qry", acNormal, acEdit

how would I run the external db query?

 

[sxschech]

I'm not really familiar with using password protected databases. As for the query, couldn't you use the "In" statement and the external database option? You can in effect have your data in db1 your queries in db2 and your output results (from maketable, append) in db3 and you wouldn't need to link a table to do so.