Overview

In the following discussion, Access queries that return data result sets (Select, Union, and Crosstab) are referred to as "Selection" queries. Access MakeTable, Append, Delete, and Update queries are termed "Action" queries and do not return any data.

When generating new MS-Access applications (MDB format) that operate with SQL Server, by default, Scriptoria implements MS-Access queries using the linked tables they include. Although linked QueryDefs are updatable, network and database performance will usually improve, sometimes dramatically, by creating MS-Access pass-through queries based on SQL Server views. Pass-through queries however, are not updatable.  You can use the Assign Options to Individual Queries form to create pass through queries. A where-used facility is provided the help determine which queries are used by forms and are, therefore not good candidates for pass through queries.

Select, Union, and Crosstab Queries

All Access "Selection" queries are converted to SQL Server views, procedures or functions. The only exception is Crosstab queries that return a variable range of values; i.e., those that do not supply Crosstab Column Headings. SQL Server procedures are used to implement these run-time dependant queries. A minimal amount of manual effort is required to incorporate run-time dependant Crosstab queries into Access front-end applications .

MakeTable, Append, Delete, and Update Queries

Converting Action queries to execute on SQL Server improves performance and operational control.

Action queries are converted to Transact-SQL procedures.

An SQL Server stored procedure is generated that implements the logic of Access Append and MakeTable queries.

Bulk Updates

After converting MakeTable and Append queries to SQL Server permanent table, it may be desirable to create a script with calls to the converted procedures which can be scheduled to run overnight. It may also be appropriate to include Delete and Update queries in the script depending on your application.

The Client-Server page of the Create SQL Server Application form contains an option to replace the original MS-Access Action queries with pass-through queries that call the converted SQL Server procedures.  There is also an option that instructs Scriptoria to remove MakeTable and Append queries from the converted application.

Scriptoria Assigned Names when Generating Applications

When Scriptoria links SQL Server tables and creates pass-through queries by appending a suffix to the end of Access names as follows: