Run query in another database

Question

Hi

Is it possible to run Query_A in Database_A from Database_B?

Thanks

Answer 1

You could use straight forward vba database automation or I also came across another example where you can add the other db as a reference within your 2nd db and call the query, see:http://www.access-programmers.co.uk/forums/showthread.php?t=156716.  It depends a little on what exactly you are trying to do.

---

Daniel Pineault, 2010-2011 Microsoft MVP

http://www.cardaconsultants.com

MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Thanks Daniel

I took the easy route and just linked the relevant tables. Then ran the query in Database_B

Answer 3

David-W-Fenton wrote:

Karl Donaubauer wrote

Set db =

DBEngine.Workspaces(0).OpenDatabase("c:\path\Another.mdb")

Just curious, but is there a difference between that and

DBEngine.OpenDatabase()? I would assume the shorter version is going

to use the default workspace, and have never used anything else.

Yes, you're right.

I have used all 3 ways (also simply OpenDatabase()) depending on

the mood or where I copied the line from ;-) (this time from my website).

A short test proves that even setting the db immediately

after appending a new workspace:

...

Set wrkJet = CreateWorkspace("mySpace", "admin", "")

DBEngine.Workspaces.Append wrkJet

Set db = DBEngine.OpenDatabase("Some.mdb")

or

Set db = OpenDatabase("Some.mdb")

a loop through the databases collections shows that

the default workspace is used.

A different workspace is only used when stated explicitely:

Set db = DBEngine.Workspaces(1).OpenDatabase("Some.mdb")

or

Set db = Workspaces("mySpace").OpenDatabase("Some.mdb")

etc.

So there's no need to do more than

Set db = OpenDatabase()

---

cu

Karl

*******

Access-FAQ (German/Italian): http://www.donkarl.com

Answer 4

Karl Donaubauer [MVP] wrote in

news:*** Email address is removed for privacy ***

m:

Set db =

DBEngine.Workspaces(0).OpenDatabase("c:\path\Another.mdb")

Just curious, but is there a difference between that and

DBEngine.OpenDatabase()? I would assume the shorter version is going

to use the default workspace, and have never used anything else.

---

David W. Fenton                  http://www.dfenton.com/ contact via website only     http://www.dfenton.com/DFA/

Answer 5

Thick Mike wrote:

Is it possible to run Query_A in Database_A from Database_B?

The best method depends on the type of the query.

If it's an action query you can do it with a view lines of e.g. DAO code:

Dim db As DAO.Database

Set db = DBEngine.Workspaces(0).OpenDatabase("c:\path\Another.mdb")

db.Execute "YourQueryNameInTheOtherDB", dbFailOnError

---

cu

Karl

*******

Access-FAQ (German/Italian): http://www.donkarl.com