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).OpenDatabase(modGlobal.g_strDatabasePath).

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).OpenDatabase(modGlobal.g_strDatabasePath1) to create a second database variable, so all I need to know is how the query syntax should identify which database each table is in.

Buck_BeasomDatabase DesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<Is there a way to create a query that joins tables that reside in multiple back-end linked databases?>
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...

Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
Buck_BeasomDatabase DesignerAuthor Commented:
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(strSQL) so I can query the database. (This, of course, is after declaring both the rsRecordset and dbDatabase as variables.)

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.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
If you just use CurrentDB to set your db object, you can specify all your tables in the query without worrying about which back-end they're in.  You don't need to specify the physical back-end database name - Access uses the linked table information to query them.

By the way, building the Select clause in VBA can lead to very difficult debugging problems.  If at all possible, it's better to have a saved query that has the basic structure you need, then modify it with the correct Where and Order By clauses.

We've written code to automatically find and replace the Where clause in a SQL statement.  It's a free download called "J Street SQL Tools" on our free J Street Downloads page at

Take the text and paste it into a new module called basJStreetSQLTools.

To use it, try the function ReplaceWhereClause.  You send in a whole SQL statement and the new desired Where clause, and it locates and snips out the old one, inserts your new one, and gives you back the new statement.  If you send in a null or empty Where clause, the function just removes any existing one from the statement.

Rebuilding a whole SQL Statement in code is laborious and often leads to hard-to-diagnose errors.  Our replacement technique allows you to store the whole query structure in a property or saved query without having to rebuild it each time in code.  All you have to do is rebuild the Where clause.  It's one of the most useful functions we've ever written - it's used in every single Access application we build.  It works with both Access and SQL Server query syntax, so it works fine on passthrough queries too.

By the way, there's also a ReplaceOrderByClause function that does the same for sorting.

Armen Stein

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Buck_BeasomDatabase DesignerAuthor Commented:
Cool stuff. I will take a look and give it a try. Will let you know how it works out.

Buck_BeasomDatabase DesignerAuthor Commented:
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.

Buck_BeasomDatabase DesignerAuthor Commented:
See my last comment.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.