There are two questions to answer with regards to translating SQL.
Firstly will you be converting all the queries to SQL Server views and stored
procedures, or will you just convert blocks of queries which cause performance
issues as you go through testing the application? MUST supports both approaches.
The second question is have you already upsized the database using MUST? If you
are not using the same copy of MUST used to upsize the data, 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 all users to have these same permissions on the
general tables. MUST also has an option to place all converted SQL in a separate
schema.
| existing SQL Server |
Load SQL Server data dictionary into MUST, if you have NOT just finished
upsizing the data.
Enter the server name 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 will be prompted to include forms and reports, to
re-package SQL behind forms and reports into views; .normally you will NOT
want to do this unless moving towards a .net solution.
Analysis should normally take from 1 to 10 minutes, firstly all the SQL is read
and then a series of processing steps executed to translate the SQL. During
analysis MUST+SQL will open up your database ( so switching OFF any startup form
or autoexec macro is recommended before starting the analysis). |
| query dependencies |
Review the query hierarchy, this simply generates useful documentation on how
your queries are structured. |
| parameters |
Review the parameters identified during Analysis. Displayed are a consolidated
list of parameters matching your screen controls and any query parameters.
You need to check that the parameter data types are correct; as incorrect data
types can cause type conversion issues once views are converted to SQL Server.
Review and make any required changes to data types. |
| manage functions |
Review any custom VBA functions. The system 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 our built in VBA support library to help you,
this support library will be created in your database as a series of SQL
functions to support Access functions without a direct equivalence to a single
SQL Server function. |
| missing tables |
Broken queries, or queries which have missing tables are excluded from
conversion, a list of missing tables is provided with details of the excluded
queries.
This useful feature can be used to tidy up an Access database which has design
objects that no longer work.
|
| renamed links |
Where an existing Access database has links to other Access databases and linked
tables have been renamed the system makes corrections for the renaming of these
linked tables. |
| convert queries |
Build the required views and stored procedures. You can select and build all
queries, or choose individual queries.
When choosing individual queries MUST will identify any dependent parent queries
and also select these for conversion.
The SQL for Queries which fail to convert can be directly edited and
re-submitted in the tool.
|
| set test parameters |
A test harness is provided, 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 and then
execute the tests to check for problems.
|
| test queries |
Executes a test for the supplied parameters.
Problems can normally be fixed using the SQL Server CONVERT or CAST functions
which change between data types.
The SQL for Queries which fail to test can be directly edited and re-submitted
in the tool.
|
| relink Access |
Re-link the Access application. MUST+SQL 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+SQL 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 |
TSQL script files can be generated for the queries, this can be very useful in
resolving issues where some queries have failed to correctly convert to SQL
Server. |
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.