MS access query multiple MS Access Databases with relative path

Question

I am trying to setup an access database (Queries.accdb) that will be used to query multiple access databases as table sources. I am able to do it by hardcoding, but I am getting syntax errors when I try to use relative paths. I would like to be able to move the Queries.accdb around.

Current file structure:

C:\Temp\PB\28\A.accdb
C:\Temp\PB\28\B.accdb
C:\Temp\PB\28\Queries.accdb
C:\Temp\PB\29\A.accdb
C:\Temp\PB\29\B.accdb
C:\Temp\PB\29\Queries.accdb

Current working query:

SELECT * 
FROM  [;database=C:\Temp\PB\28\A.accdb].[Catalog] AS a 
INNER JOIN [;database=C:\Temp\PB\28\B.accdb].[Catalog] AS b 
ON a.componentid = b.externalid;

When I try different variations (.\ ~\, etc.) to remove the hardcoded path in front of the DB file, it automatically adds the "default database folder" setup in Access. (example: "C:\" would change the following query to "C:\A.accdb")

SELECT * 
FROM  [;database=A.accdb].[Catalog] AS a 
INNER JOIN [;database=B.accdb].[Catalog] AS b 
ON a.componentid = b.externalid;

I created a VBA function to return both the full "from" bracket (including database file) and also one just returning the path, but I cannot figure out the correct syntax to use VBA functions in the from clause.

"[;database=" + Application.CurrentProject.Path + "\A.accdb]"
"Application.CurrentProject.Path"

Gustav · Accepted Answer · 2016-04-27 08:18:24Z

1

I cannot figure out the correct syntax to use VBA functions in the from clause.

That's because you can't. The path must be resolved before calling the query.

So you have to build the finished SQL string in VBA, then call the query.

That said, wouldn't it be much simpler to just link the tables needed for the current operation, assigning them names as CatalogA and CatalogB?

The (re)linking is easily done in VBA and would free you completely from tampering with the queries.

answered Apr 27, 2016 at 8:18

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS access query multiple MS Access Databases with relative path

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related