ACC2000: Creating Virtual Indexes with SQL Data-Definition Queries

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.

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: 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.

For more information about creating indexes, click Microsoft Access Help on the Help menu, type CREATE INDEX Statement in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.


For more information about data-definition queries, click Microsoft Access Help on the Help menu, type data-definition in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.