Join Query Between Different Linked Databases

Posted on 2013-08-27
Medium Priority
Last Modified: 2013-08-27
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.

Question by:Buck_Beasom
  • 4
  • 2
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39443595
<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...

ID: 39443607
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

Author Comment

ID: 39443889
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Accepted Solution

Armen Stein - Microsoft Access MVP since 2006 earned 2000 total points
ID: 39443932
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 www.JStreetTech.com/downloads.

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

Author Comment

ID: 39443951
Cool stuff. I will take a look and give it a try. Will let you know how it works out.


Author Comment

ID: 39444106
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.


Author Closing Comment

ID: 39444108
See my last comment.


Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question