Microsoft KB Archive/112131

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

Article ID: 112131

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q112131



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 Microsoft Access queries by creating a view on the server and then linking (attaching) it. The Microsoft Jet database engine treats a linked (attached) 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 Microsoft 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 Microsoft Access which fields uniquely specify a record returned by the view. This lets the Microsoft Jet database engine create an updatable recordset on the view, and on Microsoft Access queries and forms that use it.

For example, you could run the Microsoft Access data-definition query

  CREATE UNIQUE INDEX index1 ON SeptemberOrders (OrderID)

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 Microsoft 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 Microsoft Jet database engine.

NOTE: If you are using the SQL Server driver (version 2.65) that is installed with Microsoft Access 97, you will be 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.

