Sorry for a late response, was up in Vail, Colorado all last week. Now back to finish something with a SQL 2008 (Access 2010) conversion. Wanted to share this in case the fields and indexes are being updated on the SQL Server. After running some performance test, the results were not as expected. This is a good reason in my case to write code to re-attach tables.
From MSDN: The most efficient way to handle server data is to attach SQL tables and views from the server. Microsoft Access stores field and index information for attached tables. This improves performance when you open the tables.
Note that you must re-attach remote tables if you make changes to fields or indexes on the server. (an "ah-hah momemt for me)
One more query migration option: Using SQL Pass-Through Queries
In many applications, you can use both Microsoft Access queries based on remote tables and SQL pass-through queries. Pass-through queries offer the following
advantages:
- Microsoft Access does not compile a pass-through query. It sends the query directly to the ODBC server, speeding up the application.
- Microsoft Access sends the pass-through query to the server the way you enter it. Therefore, more processing occurs on the server and less data is exchanged over the network.
- Forms, reports, and Microsoft Access queries can be based on pass- through queries using ODBC attached tables.
- You can use SQL Server-specific functionality, such as stored procedures and server-based intrinsic functions that have no equivalent in MSAccess VBA code.
- Update, delete, and append pass-through action queries are much faster than action queries based on attached remote tables, especially when many records are affected.
Pass-through queries have the following
disadvantages:
- A SQL pass-through query always returns a snapshot that cannot be updated. A Microsoft Access query usually returns a dynaset that reflects other users' changes and can be updated.
- You type the commands directly into the SQL Pass-Through Query window with the syntax that your server requires. You cannot use the graphical query by example (QBE) grid.
- To use a parameter with a pass-through query, you must run the query in code and modify the query's SQL property.
Moving a query from MS Access and re-creating the query (view) and storing it on the SQL Server has the advantage that the SQL Server queries are precompiled so do not have to rebuild an execution plan every time they run. This is not the case with a Pass-Through Query.
The Pass-Through query is just another option. In general, it is probably not as common in MSAccess applications.
