In an application that uses linked SQL Server tables, two different database engines are at work: the Office Access/Jet database engine that runs on the Office Access client and the SQL Server database engine.
The interaction of these two engines can sometimes yield results that are inferior to those obtained by using only the Jet database engine with native Office Access tables.
This white paper discusses several of these issues and presents strategies for resolving them.
Most of these issues relate to performance or updatability.
Suppliers, and that explicitly names the three columns in the table. In the first step, Office Access picks a "bookmark" column or set of columns, which is usually the table's primary key but could be based on any unique index, and retrieves just those values for every row in the table or query. Then Office Access prepares a parameterized SQL statement to select all the columns in the table or query for 10 rows at a time.
In this example, there are only three rows in the table, so the final bookmark value, 3, which corresponds to the last Shipper ID in the table, is submitted eight times, because the rows are always fetched in sets of 10.
The SQL Server Migration Assistant (SSMA) for Office Access enables you to convert an Office Access database to this type of application by moving your Office Access data to new SQL Server tables and linking to these tables.
Any forms, reports, queries, or code that previously worked with the original Office Access tables are automatically connected to the new SQL Server tables.
To enable tracing of all ODBC commands from the Jet database engine: After making this change in the registry, queries submitted to any ODBC data source are logged in a text file named
Unless you delete this file or its contents, it continues to grow as new queries are executed and the tracing activity degrades performance. SQLExecute: (MULTI-ROW FETCH) SQLExec Direct indicates execution of a non-parameterized query.
Andy Baron November 2006 Applies to: Microsoft SQL Server 2005 Summary: One way to create applications that use Microsoft Office Access for creating user interfaces and that use Microsoft SQL Server for data storage is to link Office Access tables to SQL Server tables.