MUST Logo








Query Migration Menu

Walk-through of best practise when convering queries to views and stored procedures

MUST+SQL, CLICK HERE for a short video demonstration of converting SQL from Access to SQL Server.

MUST+SQL, View a pdf file showing advanced SQL Translation Features.

There are two questions to answer with regards to translating SQL.

Firstly will I be converting all my queries to SQL Server views and stored procedures, or will I just convert blocks of queries which cause me performance issues as I go through developing my application. MUST supports both approaches.

The second question is have I already upsized my database using MUST? If you do not have the copy of MUST available, or the SQL Server design has changed, or you didn’t use MUST in the first place then you need to load the data dictionary into MUST, a simple process and essential for validating the SQL during translation.

A special schema (default name Reporting) is created into which MUST will generate a parameter table and view, for handling your Access references to screen controls or query parameters (MUST automatically makes these operations multi-user by using a user specific parameter view). This is placed on a special schema, because users will need INSERT and UPDATE permissions on the parameter table, but you may not want them to have these permissions on the general tables. MUST also has an option to place all converted SQL in a separate schema.

existing SQL Server Load data dictionary into MUST, if you have not just finished upsizing. Enter the server and database name and then use the'load' button at the bottom of the screen. MUST needs to know about all the tables used by the application to validate all the SQL.
Access Application Select the Access application to analyse.
analyse Application Analyse the database . You need to decide whether you want to re-package SQL behind forms and reports; .normally you will not want to do this unless moving further towards a .net solution. Analysis should normally take here from 1 to 10 minutes, firstly all the SQL is read and then a series of processing steps executed to translate the SQL. MUST will also open up your database (switching OFF any startup form or autoexec macro is recommended).
query dependencies Review the query hierarchy, this simply generates useful documentation on how your queries are structured.
paramters Review the parameters identified by MUST. MUST produces a consolidated list of p arameters matching your screen controls and parameterised queries. You need to check that the data types are correct, as this can cause type conversion issues when working in SQL Server. Review and make any required changes to data types.
manage functions Review any custom VBA functions. MUST will create a stub function for you, at this point checking the data types for parameters and equally importantly return data types should be undertaken. If you see SQL_VARIANT data types then be aware that this often means you did not specify the data type in Access, at this point it is a good idea to assign an explicit data type, for example INT or VARCHAR(10). You will also at some point need to re-write your VBA functions in TSQL, feel free to extract example code from ou built in VBA support library which will be created in your database as a series of SQL functions.
missing tables MUST will also automatically exclude any broken queries, or queries which have missing tables, a list of missing tables is provided with details of the excluded queries.
renamed links MUST also indicates where any linked tables have been renamed, and makes corrections for the renaming of Access links for this. This is only for information.
convert queries Build the required views and stored procedures. You can either select and build everything, or choose individual queries. When choosing individual queries MUST will identify any dependent parent queries and also select these for conversion.
set test parameters MUST also provides a test harness, where views are opened and tested, and stored procedures executed (inside a transaction with automatic rollback). This test harness will allow you to identify data type conversion problems and any other issues. You can enter values for your screen parameters, then run the tests to check for problems. Problems can normally be fixed using the SQL Server CONVERT or CAST functions which change between data types.
test queries Executes a test for the supplied parameters.
relink Access Re-link the Access application. MUST will run through your Access application replacing your Queries with either, a pass through query to execute the new stored procedure, or a linked view to replace all other types of queries. At this point if you have chosen to convert the SQL behind forms and reports, the record sources will be re-connected to the new linked views. MUST will also create a link to the parameter view, and you can import a testing form, which shows you how to change your interface to replace the existing screen control references using the new parameter table.
query script files

One final point is that integer arithmetic in SQL Server gives a rounded result, where as in Access it gives a decimal. You need to check for this (MUST can not detect all occurrences of this) and add CONVERT or CAST functions to shown the result as a decimal.