Avatar of Buck Beasom
Buck BeasomFlag for United States of America asked on

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.

Microsoft DevelopmentMicrosoft Access

Avatar of undefined
Last Comment
Buck Beasom

8/22/2022 - Mon
Jeffrey Coachman

<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 2006

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 Beasom

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.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Armen Stein - Microsoft Access MVP since 2006

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Buck Beasom

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

Buck Beasom

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 Beasom

See my last comment.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.