Accessing another password protected database in an SQL query in Access 97
jet, ms-access, ms-access-97, sql
Solution
Finally, after countless trial & error sessions, I've found a solution. This line seems work and also avoids placing two opening square brackets after each other:
FROM Table2 IN '' ';database=C:\db\db2.mdb;PWD=mypwd'
It's a shame that this isn't documented somewhere in a proper manner.
Problem
I am currently working on an SQL query for Access 97. Given are the following tables (simplified for demonstration purposes), each of which are located in separate mdb files: Table1 in C:\db\db1.mdb: ``` PartyId (PK) Name ------------ -------- 1 A 2 B 3 C ``` Table2 in C:\db\db2.mdb: ``` PartyId (PK) Date (PK) Value ------------ --------- ----- 1 6/30/2014 4 1 7/1/2014 8 2 5/3/2014 3 3 5/5/2014 5 3 5/3/2014 1 3 5/2/2014 2 ``` Here I would like to look for the most recent value of each party, based on a defined date. So let's say, I'd label 7/5/2014 as the target date, then my query should return the following: ``` PartyId Name Date Value ------- ---- -------- ----- 1 A 7/1/2014 8 2 B 5/3/2014 3 3 C 5/5/2014 5 ``` I have created the following query in the C:\db\db1.mdb database: ``` SELECT T.TPartyId, Name, T.TDate, T.TValue FROM Table1 INNER JOIN [ SELECT Table2.PartyId AS TPartyId, MAX(Table2.Date) AS TDate, FIRST(Value) AS TValue FROM Table2 IN 'C:\db\db2.mdb' WHERE Table2.Date <= #7/5/2014# GROUP BY Table2.PartyId]. AS T ON (Table1.PartyId = T.TPartyId); ``` The problem is that Table2 is actually located in a password protected database file. Therefore, I have tried to modify the query, as described in http://support.microsoft.com/kb/113701 , to the following: ``` SELECT T.TPartyId, Name, T.TDate, T.TValue FROM Table1 INNER JOIN [ SELECT Table2.PartyId AS TPartyId, MAX(Table2.Date) AS TDate, FIRST(Value) AS TValue FROM [;database=C:\db\db2.mdb;PWD=mypwd].Table2 WHERE Table2.Date <= #7/5/2014# GROUP BY Table2.PartyId]. AS T ON (Table1.PartyId = T.TPartyId); ``` However, this always results in a syntax error. I suspect that the follow up brackets found in the ``` INNER JOIN [ … [;database= … ] … ] ``` statement are the cause of the failure. Unfortunately Access 97 always requires aliases to be enclosed in square brackets, followed by a period, whereas Access 2000 and higher don't have this limitation. Is there any way this query can be accomplished with Access 97 nonetheless? Thanks.