ACC: Creating Virtual Indexes with SQL Data-Definition Queries

• Microsoft Access versions 2.0, 7.0, 97

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 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)

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.

REFERENCES

For more information about creating indexes, search the Help Index for "CREATE INDEX Statement," or ask the Microsoft Access 97 Office Assistant.

For more information about data-definition queries, search the Help Index for "data-definition", and then view "Work with tables or indexes by using an SQL data-definition query" or ask the Microsoft Access 97 Assistant.