为什么链接视图从MS Access与SQL Manager提供不同的结果? [英] Why does linked view give different results from MS Access vs SQL Manager?

问题描述

我在SQL Server 2000上创建了一个视图，并通过ODBC将视图链接到我的Access 2007数据库.从Access打开视图显示看起来像一个重复值.我更新了视图，以包含相关数据的ID，并且该数据已重复.

访问似乎正在为该类型的每一行显示每种类型的第一条记录.此问题提到了查看订购依据或Top子句，但我认为没有使用它们中的任何一个.该视图将8个表链接在一起，我唯一能想到的就是我视图中的某些表，但在Access中却没有链接.我也将链接这些表只是为了进行尝试，但是我认为我在Access中不需要它们.

我已经在搜索Google上的内容时感到很累，但是除了我提到的问题之外，我没有其他的运气.这样的链接视图是否正常?

这是我从sql manager获得的结果集:

id    Type          Dim1
----  ------------  ------
3111  Pipe          480   
3112  Fittings      0
3113  Pipe          1080
3114  Fittings      0
3115  Fittings      0
3116  Fittings      0
3117  Pipe          216

在访问中，我看到了:

id    Type          Dim1 
----  ------------  ------
3111  Pipe          480
3112  Fittings      0
3111  Pipe          480
3112  Fittings      0
3112  Fittings      0
3112  Fittings      0
3111  Pipe          480

解决方案

您不需要在Access中链接的所有8个表...视图就足够了，Access不需要了解基础表. /p>

我认为您遇到了其他问题，我也遇到了类似的问题.
当我通过ODBC在Access中链接SQL Server视图时，弹出一个窗口，希望我从视图的列中选择唯一标识符(读取:主键):

如果我没有选择任何内容(或某些错误的列，未标识唯一的记录)，Access会像您的示例中那样弄乱显示的数据.
显然，如果没有唯一标识符，Access将无法正确显示视图.

您的问题的解决方案:
据我了解，id是唯一的，对吧?
如果是，只需删除该视图，然后通过ODBC重新链接它，然后在弹出选择唯一标识符"窗口时，您需要选择id列.

I created a view on SQL Server 2000 and linked the view to my Access 2007 database via ODBC. Opening the view from Access showed what looked like a duplicate value. I updated my view to include the ids of the data in question and it was duplicated.

It looks like access is displaying the first record for each type for every row of that type. This question mentioned looking at the Order By or Top clauses, but I'm not using either of them in my view. The view links 8 tables together and the only thing I can think of is some of the tables I have in my view, I don't have linked in Access. I am going to link those tables also just to try it, but I didn't think I needed them in Access.

I've tired searching Google for answers, but I haven't had much luck besides the question I mentioned. Is this behavior normal for linked views like this?

This is my result set from sql manager:

id    Type          Dim1
----  ------------  ------
3111  Pipe          480   
3112  Fittings      0
3113  Pipe          1080
3114  Fittings      0
3115  Fittings      0
3116  Fittings      0
3117  Pipe          216

And in access I see this:

id    Type          Dim1 
----  ------------  ------
3111  Pipe          480
3112  Fittings      0
3111  Pipe          480
3112  Fittings      0
3112  Fittings      0
3112  Fittings      0
3111  Pipe          480

解决方案

You don't need all the 8 tables linked in Access...the view is enough, Access doesn't need to know about the underlying tables.

I think you have a different problem, I experienced similar issues like yours as well.
When I link a SQL Server view in Access via ODBC, a window pops up that wants me to select a unique identifier (read: primary key) from the columns in the view:

If I don't select anything (or some wrong columns, which do not identify a unique record), Access screws up the displayed data like in your example.
Apparently Access is not able to display the view correctly without a unique identifier.

Solution for your problem:
As I understand your view, the id is unique, right?
If yes, just delete the view, re-link it via ODBC, and when the "select unique identifier" window pops up, you need to select the id column.

这篇关于为什么链接视图从MS Access与SQL Manager提供不同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！