如果链接表存储在SQL2008-Server上，则MS-Access DISTINCTROW不再起作用 [英] MS-Access DISTINCTROW dosen't work anymore if linked tables a stored on SQL2008-Server

问题描述

我在MS-Access中有这样的查询:

I have a query in MS-Access like this:

select DISTINCTROW companies.* from companies, contacts, companies left join contacts on contacts.com_uid = companies.com_uid (This is the ms-access form of a standard "left-join")

[公司]和[联系人]是sql-server 2008上的链接视图，ODBC驱动程序是"SQL Server本机客户端10.0".这两个视图看起来都像是从*删除= 0的[公司]中选择*"和从0删除= 0的[联系人]中选择*"

[Companies] and [contacts] are linked views on a sql-server 2008, ODBC driver is "SQL server native client 10.0". Both views looks like "select * from [companies] where deleted = 0" and "select * from [contacts] where delete = 0"

结果是错误的，因为公司显示了尽可能多的联系人.

The result is wrong since companies are show as many contacts there are.

如果视图存储在SQL2000上并与ODBC驱动程序"SQL Server"链接，则一切都很好:所有公司仅显示一次.

If the Views are stored on a SQL2000 and linked with the ODBC-driver "SQL Server" everything is fine: All the companies are shown exactly once.

有什么解决方案可以再次使用DISTINCTROW获得结果吗?

Are there any solutions to get the result with DISTINCTROW again?

推荐答案

让我们停止谈论ms-access中左联接的语法.事实是，如果链接表是sql-server 2000上的视图:

Let's stop talking about the syntax of a left-join in ms-access. Fact is that if the linked tables are views on sql-server 2000:

create view [companies] as
select * from [TabCompanies] where deleted  = 0

和

create view [contacts] as
select * from [TabContcts] where deleted = 0

这些视图是ms-access 2003/2007 mdb中的ODBC链接表. 这些问题显示在

These views are ODBC-linked-tables in a ms-access 2003/2007 mdb. The questions shows up in ms-access on a query like

select distinctrow [companies].* from [companies] left join [contacts] on [companies].com_uid = contacts.com_uid] where [contacts].[function] like 'C*'

(让我们忘记其他语法，并假设左联接在没有错误或语法错误的情况下查看结果)

(lets forget that alternative syntax and look on the result assuming that the left join works without an error or syntaxerror)

此DISTINCTROW是ms-access的一项功能，在sql-server中不知道，从我的角度来看，结果与DISTINCT相同，但即使存在具有图像数据类型的列(例如示例)也可以使用.

This DISTINCTROW is a ms-access feature and not know in sql-server and for my point of view the result is the same like DISTINCT but works also even if there are columns with datatype of images par example.

我们现在在一起的总期望与Catcall一样，在他的回答中说从公司中选择*"，但事实并非如此，为什么?

All together we expect by now the same like Catcall in his answer said "select * from companies" BUT IT IS NOT, why?

这只是整个查询的摘录，对于生产来说可能没有意义，但它显示了与sql2008连接后的更改行为.

This is only an excerpt of the whole query and may be makes no sense for production but it shows the changed behaviour wehn sql2008 is connected.

这篇关于如果链接表存储在SQL2008-Server上，则MS-Access DISTINCTROW不再起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！