ACC2000: Creating Virtual Indexes with SQL Data-Definition Queries

ID: Q209123

The information in this article applies to:

Microsoft Access 2000

SUMMARY

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

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.

REFERENCES

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.

Additional query words: attaching

Keywords : kbusage kbdta QryOthr
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo