AccessVBA在SQLServer視圖上創建索引總是創建主鍵

我有一個帶有 SQL Server 后端的典型 Access 前端。我在 SQL Server 中創建了一些視圖并在 Access 中鏈接到它們。當我使用“CREATE INDEX index_name ON view_name (field_name)”時，它會創建一個主鍵，即使我沒有指定它這樣做（并且不希望它這樣做）。這是為什么？以及如何創建非主鍵索引？

uj5u.com熱心網友回復：

這是如何作業的？

任何視圖、任何鏈接表，實際上是您從 SQLServer 中點擊、使用、消費的任何東西？

所有索引都在 SQLServer 中設定為 100%。Access 客戶端不會、不能、也不會為您創建任何型別的索引。

用于指定和設定主鍵的創建索引命令？它并沒有真正在 Access 中創建索引，而只是設定和告訴訪問要使用的 PK。

實際上，當您鏈接到視圖時，會提示您在鏈接到視圖時選擇 PK。

SQLServer 視圖沒有概念，甚至沒有告訴您或什至讓您指定 PK 列的設定。造成這種情況的部分原因實際上是一個視圖可以包含多個表——所以現在哪個表來定義主鍵。事實上，如果您的視圖與 5 個表有關聯？然后實際上該視圖具有來自 5 個不同表的 5 個不同主鍵）。

因此，當您鏈接到 access 中的視圖時，您會注意到以下提示：

如果不為 pk 選擇一列？

那么你沒有PK設定。但是，您可以使用 VBA 來告訴 ACCESS 哪一行是 PK 設定。

所以，上面說我在與 GUI 鏈接時沒有選擇 PK。或者說我正在使用代碼鏈接到視圖？

然后在設定 PK 值的代碼中，我會并且可以并且應該執行以下命令：

 CurrentDb.Execute "CREATE UNIQUE INDEX IXPK ON dbo_ViewHotelsTest (ID) WITH PRIMARY"

再次：請注意上述評論。創建唯一索引不會在 Access 中創建索引。它也不會在 SQLServer 上創建索引。該命令是您如何告訴 Access 哪一列將被視為 PK 并將其視為 PK。

那么，上面的命令？

用簡單的英語：

 Please Mr. Access, will you set the PK column and we are using the above
 command to do this.

換句話說，代碼中沒有其他命令可以“告訴”Access PK 應該是什么，因此使用了 DDL sql create index 命令。但我再次強調這并沒有真正創建索引，而只是告訴 Access 使用哪一列作為 PK。

This command results in the SAME and IDENTICAL results if you select a PK during a linking of a view.

If you want to create an index in SQLServer? Then go to SQLServer, and create your index(es) in SQLServer.

FYI: As a further explanation, in 99% of cases you NEVER want, nor need, or even should even create an index on a view on the SQLServer side of things.

In EVERY case, if the base table used for the source of the view has an index that can be used, it WILL IN ALL cases be used if you build an on-the-fly query, build a SQLServer side view, or even create a sql stored procedure. IN ALL cases, a simple create of an index on the base source table (using SQLServer tools) will suffice, and in ALL cases, include views, and including linked view from Access will automatic use ANY and ALL existing indexes on the base table from SQLServer.

So, not only is there zero requirements to EVER try and create an index in Access on linked tables (or linked views), but in fact it not even possible. Of course the create index command DOES need to be used to set the PK column when linking to a view.

If you link to table, then Access can figure out which column is the PK, and will set this for you. But SQLServer does not have a setting, nor even the concept of a PK column for a view, and thus you have to select the PK during linking using the GUI, or as noted, you can in code execute the above command that tells access which column to use as the PK, and as noted, that command does not in fact even create an index, but that command is ONLY to tell Access client side which column to see/use as the PK.

You can for views that don't require you to "update" the data. So, a linked view without you selecting (or better said "setting") the PK column will be read only.

So, if you using the view for a combo box, or say just a report? Then you don't care, and don't need to set the PK for that view, and it will be "read only". So this means that you ONLY need to set the PK column for a view if you need to update that view (say in place of updating the base table that the view is based on).

So, in summary:

that create index command does not actually create an index.

That create index command is ONLY required if you need a linked view that allows Access client side to update such views. Without the setting, then the linked view will be read only. So the purpose, the act, the role, the "thing" that create index does on the linked view? It is ONLY to tell Access what column is to be used for the PK - it does not actually create an index anywhere - including NOT creating one in Access client side. (So, ONLY purpose is for TELLING access which column to use for the PK. Can't really say why they use that command that way but best guess was no other way existed to tell Access what column to use for the PK - so we use that command).

If you use the linked table manager, and re-fresh the table links? Access WILL remember the PK settings for a view. However, if during linking you change the database that the linked tables point to? Then the PK settings in views will be lost during that re-linking process. (and then you have to re-execute those commands to re-tell Access which column in the linked view is to be seen/used as PK column.

You don't need to ever create an index client side for Access in regards to linked tables, or views - all indexing is automatic, and if an index exists on the server table, it will and can be used.

So, create index command is HOW you setup a PK column for linked views. In all other cases (linked tables - but not a view), then that command is not required, and ANY and all existing indexes that exist and were created on the server side table will be used (and thus no need to try or create an index in Access, since all such indexes are handled by the server side - Access has no say, nor even control over how SQLServer uses indexes). But, a correct use of index on a SQLServer table will automatically be used by Access in the requests it makes to SQLServer. But that "job" of indexing is 100% managed by the server - not Access.

標籤：毫秒访问

上一篇：MSAccess-選擇要合并的表

下一篇：根據部分文本在MSAccess表單中搜索記錄