CODEWITHSUNDEEP
ms-accessvba
Scott Gunnell-Beck
Scott Gunnell-Beck

Reputation: 1

MS access query multiple MS Access Databases with relative path

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:

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"

Upvotes: 0

Views: 679

Answers (1)

Gustav
Gustav

Reputation: 55981

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.

Upvotes: 1

Related Questions