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.