Translates SQL (MUST+SQL/+WEB)
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:
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.
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.
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.
All the resulting Views and Stored Procedures can automatically re-mapped to a schema, isolating them from other parts of your SQL Server design.
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.
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.
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.
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.
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.
Where Access functions have direct equivalents in SQL Server, the tool substitutes existing Access functions with new
SQL Server function.
To resolve problems where calculated columns refer to other calculated columns we automatically create
nested queries.
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.
If you have an Access application linked to an existing SQL Server 2005/2008/2008R2 Express 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.
Existing Access queries can be either directly migrated to SQL Server, or SQL Server Script files can be prepared for migrating
the queries.