MUST+SQL gives you an extremely powerful SQL Translation capability, designed to migrate Access specific features such as references to screen controls like Forms!CountryForm!Country into parameter driven Views and Stored Procedures, and can also reconstruct queries into nested queries to support existing complex calculations as described in several articles on this site.

Each section below provides an outline of key product features.

Identifies all Queries in an Access Application

Point MUST at an Access Application, and it will help you to convert all or some of the queries to SQL Server Views and Stored Procedures.

Converts all Queries to Viewes & Stored Procedures

INSERT, UPDATE and DELETE queries are converted to Stored Procedures, which can either be driven using variables (which are inserted in the procedure calls), or by extracting parameters from a parameters table. Other SELECT and CROSSTAB queries are converted to Views.

Identifies all Query Parameters & references to screen parameters, such as Forms! etc.

Any reference in an existing query to a Form or pop-up Parameter Box, is identified. These are then consolidated into a single list of variable parameters. You can then review the data types associated with the parameters and make more detailed selections (Access defaults parameters to TEXT fields, for SQL Server you often need to be more specific regarding the data types, this feature allows for better control of parameter data types.

Schema support

All the resulting Views and Stored Procedures can automatically re-mapped to a schema, isolating them from other parts of your SQL Server design.

Generation of parameters table and user view

The parameter table can be made available to users and your application through a parameter view, which enables multi-user selection of parameters which drive views and are used in stored procedures with each individual users choices being preserved.

Addition of CROSS JOINS to SQL supporting existing parameters

On any query which used to use an Access parameter, the parameter table gets inserted as a CROSS JOIN, and all the old form control or Access parameter references are updated to refer to fields in the parameters table.

Adaption of SQL to multi-layered nested queries supporting complex calculations

In Access queries can make calculated fields reference other calculated fields in the same query, this is not allowed in SQL Server. MUST will however create nested queries, through this technique existing calculations are ordered in a sequence of layers, thus supporting existing complex calculations.

Identification of Query layering & dependencies

All query dependencies are identified (displayed to a maximum of 12 layers). This then enables the tool to allow you to selectively migrate any individual query and automatically identify and include the migration of all dependent query components.

Identification of VBA Function calls

The tool identifies your existing custom VBA functions used in queries, and creates a holding function in SQL Server, into which you can add additional TSQL program code, or replace with your own .NET SQL Server Functions.

Conversion of Access functions to SQL Server functions

Where Access functions have direct equivalents in SQL Server, the tool substitutes existing Access functions with new SQL Server function.

Generation of Nested SQL

To resolve problems where calculated columns refer to other calculated columns we automatically create nested queries.

SQL Server function library

A library of SQL Server functions is provided, for translating Access functions which do not have a direct equivalence in SQL Servers built in functions. This function library is under development at present, and more functions will be added in the near future. Domain functions such as DLOOKUP are automatically translated directly into SQL and inserted back into the queries.

Converts queries for Access databases already linked to SQL Server 2005/2008

If you have an Access application linked to an existing SQL Server 2005/2008 database, then MUST can automatically load the SQL Server data dictionary (list of all tables and fields in the SQL Server) to enable either all or a selected number of Access queries to be migrated. Support for SQL Server 2000 will soon be available.

Generation of Script Files

Existing Access queries can be either directly migrated to SQL Server, or SQL Server Script files can be prepared for migrating the queries.