Join Query Between Different Linked Databases
Is there a way to create a query that joins tables that reside in multiple back-end linked databases? I have some databases that are growing beyond the 1.5MB maximum size, and our experience shows that this is where performance starts to suffer.
Right now I use syntax like this:
Set dbDatabase = DBEngine.Workspaces(0).Ope
At that point, I can do Select or Update queries just as if the tables were in the native database. I'd like to be able to do that where one table is in one dbDatabase Variable and the other one is in another. I'm assuming that I could use:
Set dbDatabase1 = DBEngine.Workspaces(1).Ope
Thanks.
Right now I use syntax like this:
Set dbDatabase = DBEngine.Workspaces(0).Ope
At that point, I can do Select or Update queries just as if the tables were in the native database. I'd like to be able to do that where one table is in one dbDatabase Variable and the other one is in another. I'm assuming that I could use:
Set dbDatabase1 = DBEngine.Workspaces(1).Ope
Thanks.
Why aren't you just using linked tables? You can join linked tables in queries regardless of which back-end databases they are in.
Side note: If your database are getting that big, you might want to consider SQL Server. Even the free Express edition can hold a database up to 10GB.
Armen Stein
Side note: If your database are getting that big, you might want to consider SQL Server. Even the free Express edition can hold a database up to 10GB.
Armen Stein
ASKER
OK. I'm actually responding to both suggestions. I probably wasn't clear.
I am already using linked tables, and I actually have several different linked databases. But when I use embedded SQL in code (which I do A LOT), I have to first declare a database variable and fill it with the database path. Then I can use
Set rsRecordset = dbDatabase.OpenRecordset(s
If the strSQL embedded query is pulling from tables in more than one back end database, I don't know how to tell the code which back end database the table is in as "dbDatabase" can only refer to one .accdb.
I use the embedded SQL method because very often the strSQL variable is assembled from a combination of explicitly named fields in the tables AND variables that are established either by user entered criteria or other embedded logic.
I'd love to make the move to SQL Express, but the overhead requirements are much broader than Access and I need some ramp up time. Also, I may run afoul of the IT people who often get stroppy when someone goes outside the lines.
Thanks.
I am already using linked tables, and I actually have several different linked databases. But when I use embedded SQL in code (which I do A LOT), I have to first declare a database variable and fill it with the database path. Then I can use
Set rsRecordset = dbDatabase.OpenRecordset(s
If the strSQL embedded query is pulling from tables in more than one back end database, I don't know how to tell the code which back end database the table is in as "dbDatabase" can only refer to one .accdb.
I use the embedded SQL method because very often the strSQL variable is assembled from a combination of explicitly named fields in the tables AND variables that are established either by user entered criteria or other embedded logic.
I'd love to make the move to SQL Express, but the overhead requirements are much broader than Access and I need some ramp up time. Also, I may run afoul of the IT people who often get stroppy when someone goes outside the lines.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool stuff. I will take a look and give it a try. Will let you know how it works out.
Thanks.
Thanks.
ASKER
I'm putting this comment in first so you get it, before accepting your solution - which solved my problem. The CurrentDB method (and this I didn't know) does indeed allow me to link multiple back end tables into a single query. This is HUGE.
I am going to check out your J street tools, but this will probably not stop me from continuing to use embedded SQL - even for Select queries - in code. What you are suggesting is basically the equivalent of "Stored Procedures," which is fine for certain circumstances. But in reality, debugging Select queries in SQL is not all that difficult, even if they are really complicated. It used to annoy me that the "Add Watch" feature only gives you the first 255 characters (unlike VB 2010, where you can see the whole thing.) But then I realized that if I simply populated the SQL into a cell on an Excel worksheet, I could cut it out, paste it into the query builder and easily find where any problems might be.
Because I use all sorts of array techniques, query assemblies that use the same underlying SQL but with lots of nuances and tons of criteria that can actually influence the other fields that get selected, using hard coded queries - even where the actual selection criteria can be modified - I would still need to maintain multiple versions of the queries. I've been doing this for so long that assembling the Select SQL in code has become second nature.
This is not to say that I won't check out your resource. I'm always looking for shortcuts and better methods. Anyway, you solved my immediate problem and saved me a huge amount of decision making on what tables would have to go into which back end .accdbs.
Thanks.
I am going to check out your J street tools, but this will probably not stop me from continuing to use embedded SQL - even for Select queries - in code. What you are suggesting is basically the equivalent of "Stored Procedures," which is fine for certain circumstances. But in reality, debugging Select queries in SQL is not all that difficult, even if they are really complicated. It used to annoy me that the "Add Watch" feature only gives you the first 255 characters (unlike VB 2010, where you can see the whole thing.) But then I realized that if I simply populated the SQL into a cell on an Excel worksheet, I could cut it out, paste it into the query builder and easily find where any problems might be.
Because I use all sorts of array techniques, query assemblies that use the same underlying SQL but with lots of nuances and tons of criteria that can actually influence the other fields that get selected, using hard coded queries - even where the actual selection criteria can be modified - I would still need to maintain multiple versions of the queries. I've been doing this for so long that assembling the Select SQL in code has become second nature.
This is not to say that I won't check out your resource. I'm always looking for shortcuts and better methods. Anyway, you solved my immediate problem and saved me a huge amount of decision making on what tables would have to go into which back end .accdbs.
Thanks.
ASKER
See my last comment.
Thanks!
Thanks!
If I am understanding your question, ...No
You can create one database and link all the tables from all of the disparate database there.
Then you can create your queries in this database and join the tables...
JeffCoachman