Accessing another password protected database in an SQL query in Access 97

Question

I am currently working on a SQL query for Access 97. The following tables (simplified for demo purposes) are listed below, each of which is located in separate mdb files:

Table 1 in C: \ db \ db1.mdb:

PartyId (PK)    Name
------------    --------    
1               A
2               B
3               C

Table 2 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 find the latest value of each party based on a specific date. So, let's say I would call 7/5/2014 as the end 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 created the following query in database C: \ db \ db1.mdb:

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 the password protected database file. So I tried to change the request, 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 leads to a syntax error. I suspect the following brackets found in

INNER JOIN [ … [;database= … ] … ] 

Operation

is the cause of the failure. Unfortunately, Access 97 always requires the inclusion of aliases in square brackets followed by a period, while Access 2000 and above do not have this limitation. Is there a way that this request can be achieved with Access 97 nonetheless? Thank.

Answer 1

Finally, after countless trial and error, I found a solution. This line seems to work, and also avoids placing two square square brackets one after another:

FROM Table2 IN '' ';database=C:\db\db2.mdb;PWD=mypwd'

Shame that this is not documented somewhere correctly.

Answer 2

Create Qry1 ="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  "

SELECT T.TPartyId, Name, T.TDate, T.TValue
FROM Table1 INNER JOIN Qry1
ON (Table1.PartyId = Qry1.TPartyId);