run query in another database

L

lala

Registered User.
Local time
Today, 09:15
Joined
Mar 20, 2002
Messages
741
can i run a query in another database from VB?
if so, how can i tell it which database?

thank you
 
Hello lala!
Look at "WAutomation" (Zip).
There are 2 mdb (DemoAutomationA2002x1" and "DemoAutomationA2002x2"), and word doc. "WAutomation". Put it all in the same directory.
In ..x1.mdb there is a "Query1", and in "Module1" there is Function "FOpenQuery()". You want open this query from ..x2.mdb.
Do next:
Open ..x2.mdb
Open VBA in "Form1a".
Click on Tools, References.
In Referencis form click on Browse.
In "Add Reference" form choose directory where you put these two mdb,
(look at word doc. picture 1).
Choose "Microsoft Access Databases" (mdb), (look at picture 2).
Choose "DemoAutomationA2002x1" (picture 3).
Click on OPEN.
Now in form "References" you have got (look at picture 4).
Click on OK.
Open "Form1a" in ...x2.mdb and try.
 

Attachments

Last edited:
Good example MStef.
Question, wouldn't it be easier to link the table(s). Then they would be accessable like any other table. It all depends on how often you need to do this function, I guess.
 
Hello MagicMan!

Yes, it would be easier, and he have to copy a query. There are many solutions to solve one problem. Lala asked this, and I have told them how to solve what he want.
 
Lala he?
I'm a she, can't you tell from the nick? Lolol

That being said, thank you both of you, especially link the tables, that was so easy, how I didn't think of that
 
Smiles, so sorry Ms lala...
If linking in a large environment, security, password handling, etc must be consisdered....just a note for others reading the post.
 
MStef,

Thank you for this example. Just what I needed. Linking tables not an option because of the size of the tables.
 
Findings for those who may need it.
The form in ...x2.mdb is executing the query defined in modules in ...x1.mdb. That is then executing the query defined in queries.

Tests run:
I created two additional queries in ..x1, in the queries section, queryA and queryB.

In ...x1 Modules, I created the following functions

Function FOpenQuery1()
DoCmd.OpenQuery "QueryA"
End Function
Function FOpenQuery2()
DoCmd.OpenQuery "QueryB"
End Function

In ...x2 Form, I modified the code behind the query button as follows:

Private Sub Command7_Click()
FOpenQuery1
End Sub

I also added another button to the form with the following code behind it:

Private Sub Command8_Click()
FOpenQuery2
End Sub

When running the form, I got results for QueryA, and QueryB when the respective buttons were pushed.

Conclusion: From ...x2 form, control is going to ...x1 modules.

MStef, I'm assuming that if ...x2 modules had a function of the same name that would be executed instead of looking for the one in ...x1. Do you know if there is any syntax to explicitely identify which one to execute in this scenario?

Cheers,
 
MStef,

Expanding the example one step further, I wanted to test out calling a query in two different modules.

Initially I just cloned your .x1 database to .x3. When I followed the instructions in adding a reference, and selected .3, the entry being added (again) was the .1 entry but with a location point to .x3. I got a "Name conflicts with existing module, project, or object library." There must be some internal name maintained when a database is copied.

I then tried creating a new database .3B. I then imported what was in .3. Adding the references here worked OK and I was able to execute queries from two additional databases.

Question for the group:

What is the internal name retained when a database is copied?
Can it be modified? If so, how?

Thanks,
 
Hello DonWReynolds!
I'm very glad that my example helped you.
Now, I have got no time to exploring it on.
 
You must log in or register to reply here.

Similar threads

I
Solved Confused on what Form event(s) to use that display form with only statis text before VBA code executes
Replies
14
Views
610
isaksp00
I
H
Solved Method of selecting specific records
2
Replies
20
Views
800
Harris@Z
H
B
Synchronise database
2
Replies
26
Views
1,783
Pat Hartman
P
M
Solved Task Scheduler Automation
2
Replies
22
Views
1,128
Pat Hartman
P
D
Solved Entering Data in Subform creates a OLE Server or Active X Control Error
Replies
5
Views
544
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom