5

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.

Aurora
  • 1,334
  • 8
  • 21
  • I suspect an old bug. It is a long time since I used '97, but have you tried editing in SQL view and replacing `INNER JOIN [...]` with `INNER JOIN (...)` This used to work in 2000, but as soon as you opened in design view, the query would be broken again. – Fionnuala Jul 05 '14 at 22:38
  • @Remou Yes, I've observed that behavior with Access 2000 as well. Unfortunately, Access 97 doesn't support the `INNER JOIN (...)` syntax and classifies it as a syntax error. Therefore, I am forced to use the `INNER JOIN [...]` syntax. – Aurora Jul 06 '14 at 11:34
  • Just guessing .., have you tried ( ... ) in code, rather than query design window? – Fionnuala Jul 06 '14 at 11:44
  • @Remou When I create the query in code (e.g. in VBA via the ADO interface), whilst making use of the `INNER JOIN (... [;database=...] ...)` syntax, the restriction goes away. This is the same behavior exhibited by Access 2000 and higher. So it seems that this syntax relates to a legitimate JET statement, which Access 97 refuses to support. Unfortunately, outsourcing the query into code is not a viable option, since the task I'm working on requires the query to be embedded in the access database. – Aurora Jul 06 '14 at 13:13
  • 1
    I meant could you use DAO to build the saved query query : `qdf.sql = ...` ? It is just a vague hope. – Fionnuala Jul 06 '14 at 15:37
  • @Remou I tried this, i.e. creating a query via the `DAO.Database.CreateQueryDef()` method. Unfortunately, VBA rejects it as well. However, I've found a solution to the problem (see answer). Thanks for your suggestions. – Aurora Jul 07 '14 at 17:34

2 Answers2

7

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.

Aurora
  • 1,334
  • 8
  • 21
-1
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);
Kirk Broadhurst
  • 27,836
  • 16
  • 104
  • 169
Tartarin
  • 1