Microsoft KB Archive/209123

= ACC2000: Creating Virtual Indexes with SQL Data-Definition Queries =

Article ID: 209123

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209123



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
If your server supports the creation of SQL views, you can achieve a compromise between pass-through and Access queries by creating a view on the server and then linking it. The Microsoft Jet database engine treats a linked view exactly like a linked table (with no indexes). The processing defined in a view is always performed by the server, no matter what the Jet database engine decides to execute locally.



MORE INFORMATION
If your server supports updating through views, you need to create an index specification on the attachment to tell Access which fields uniquely specify a record returned by the view. This lets the Jet database engine create an updatable recordset on the view, and on Access queries and forms that use it.

For example, you could run the following Access data-definition query:   CREATE UNIQUE INDEX index1 ON SeptemberOrders (OrderID) It is run on a linked view named SeptemberOrders that returns a subset of the remote Orders table and has a unique field named OrderID.

This query does not create anything on the server, nor does it take any extra space in your Access database. It simply tells the Jet database engine that the OrderID field is the primary key for the linked view. Note that this index is called a "fake" or "pseudo" index in that it does not improve query performance. It is only used to serve as a reference for the Jet database engine.

NOTE: You are prompted to specify a unique record identifier when linking to a view. This can be a single field or a combination of fields and will allow you to update the view without creating an index.

