在Access 97中的SQL查询中访问另一个受密码保护的数据库 [英] Accessing another password protected database in an SQL query in Access 97

问题描述

我目前正在为Access 97进行SQL查询.给出了下表(为演示目的而简化)，每个表都位于单独的mdb文件中:

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:

C:\ db \ db1.mdb中的Table1:

Table1 in C:\db\db1.mdb:

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

C:\ db \ db2.mdb中的Table2:

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

在这里，我想根据定义的日期寻找各方的最新价值.假设我将2014年7月5日标签为目标日期，那么我的查询应返回以下内容:

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

我已经在C:\ db \ db1.mdb数据库中创建了以下查询:

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);

问题在于Table2实际上位于受密码保护的数据库文件中.因此，我已尝试按照 http://support.microsoft.com/kb/中所述修改查询. 113701 ，如下所示:

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= … ] … ] 

声明是失败的原因.不幸的是，Access 97始终要求将别名括在方括号中，后跟一个句点，而Access 2000及更高版本则没有此限制.尽管如此，使用Access 97可以完成此查询吗?谢谢.

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.

推荐答案

最后，经过无数次&错误会话，我已经找到了解决方案.这条线似乎可行，并且还避免了两个开方括号的放置:

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.

这篇关于在Access 97中的SQL查询中访问另一个受密码保护的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！