Access 2007到Oracle 10g链接表-查询有缺陷的结果，但未引发任何错误 [英] Access 2007 to Oracle 10g linked table -- query with flawed results, but no errors thrown

问题描述

使用WHERE子句过滤掉不需要的记录时，查询链接的oracle 10g表的Access 2007数据库返回有缺陷的结果集.奇怪的是，正在进行 some 过滤，但操作不可靠.

Access 2007 databases querying linked oracle 10g tables are returning flawed result sets when using the WHERE clause to filter-out unwanted records. Oddly, some filtering is happening, but not reliably.

我可以可靠地演示/产生这样的问题:

I can reliably demonstrate/produce the problem like this:

1. 使用Access 2007创建一个* new *数据库.
2. 使用Access 2007创建第二个* new *数据库，然后使用另存为" 2000.
3. 使用旧版本的Access创建第三个* new *数据库.
4. 在每个数据库中运行以下查询:

SELECT 
    STATUS, 
    ID, 
    LAST_NAME, 
    FIRST_NAME
FROM 
    Oracle10g_table
WHERE 
        STATUS="A" 

• 在两个使用Access 2007创建的数据库中，运行此查询将为您提供一个结果集，其中(STATUS ="A")= false的记录中的 some 已被过滤掉，但没有全部.
• 在使用较旧版本的access创建的数据库中，where子句会正确过滤，并且结果集正确.
• 状态是一个文本字段
• 该表是与Oracle10g数据库的链接"表
• 该表有68k行
• 我已经测试了60、1000和0的超时时间
• In both databases created with Access 2007, running this query will give you a result set in which some of the records where (STATUS="A") = false have been filtered out, but not all of them.
• In databases created with older versions of access, the where clause filters properly, and the result set is correct.
• STATUS is a text field
• The table is a "linked" table to an Oracle10g Database
• The table has 68k rows
• I've tested my timeout at 60, 1000 and 0

有人遇到这个问题吗?

Has anyone run into this problem?

我想知道这是否是一种新的访问功能"，它也将影响2010年.这与ODBC有什么关系吗?

I wonder if this is a new "feature" of access that will also affect 2010. Could this have anything to do with ODBC?

感谢您的帮助， -戴夫

Thanks for any help, - dave

我只是尝试了另一种查询形式，使用HAVING而不是WHERE，它起作用了！问题是，除了这不应该改变任何东西(是的，更多的虚拟表，但不应该改变最终结果)，我的最终用户将使用Access 2007可视化查询设计器，而不是直接键入SQL，这将默认将他们输入到WHERE中的所有条件设置为

I just tried an alternate form of the query, using HAVING instead of WHERE, and it worked! Problem is, besides that this shouldn't change anything (yes -- more virtual tables, but shouldn't change the end result) my end-users will be using the Access 2007 visual query designer, not typing SQL directly, which is going to default any criteria they enter into a WHERE.

推荐答案

事实证明这是与ODBC相关的问题.我们的技术支持服务部​​门在每个工作站上都​​安装了连通性-他们自己对其进行编程，并且谁知道他们实际输入的内容-但是最终的结果是，当您使用Access(任何版本)链接到ODBC数据源时，我们的网络服务器将显示在机器数据源"标签中，因此，我们只需单击所需的那一个即可，然后就离开了.在Access 2007之前，它一直运行良好.

This turned-out to be an ODBC-related issue. Our tech support service unit installs connectivity on each of our workstations -- they program it themselves, and who knows what they actually put into it -- but the net result is that when you link to an ODBC datasource with Access (of any version), our network servers show-up in the 'machine data source' tab, so we just click on the one we want and away we go. This has worked well until Access 2007.

我所做的是创建一个新的"机器数据源，该数据源让我自己选择ODBC驱动程序(而不是使我使用我们技术支持人员创建的驱动程序).我选择了"Microsoft ODBC for Oracle"，输入了我想要的服务器的名称，这一切就花了.现在解决了WHERE子句不一致的过滤问题(希望如此).

What I did was create a "new" machine data source, which let me choose the ODBC driver myself (instead of making me use the one our tech support folks created). I picked "Microsoft ODBC for Oracle", entered the name of the server I wanted, and that all it took. Now the WHERE-clause inconsistent filtering problem is solved (I hope).

剩下的唯一事情就是将其发送回给我们的技术支持人员，以便他们清理安装.我应该索取危险津贴吗? :-)呵呵

The only thing remaining is to send this back to our tech support folks, so they can clean-up their installation. Should I ask for hazard pay? :-) hehe

这篇关于Access 2007到Oracle 10g链接表-查询有缺陷的结果，但未引发任何错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！