There are eleven main Components to the conversion process using the 2SQL Methodology, ten of which are automated by 2SQL:
Table Schema – Table field types, indexes, default values, validation rules and relationship constraints all require conversion to their SQL Server equivalents. This conversion component is usually 100% automated by 2SQL.
Table Data – The data contained in each table within Microsoft Access must be migrated to the new and equivalent tables in SQL Server. This conversion component is usually 100% automated by 2SQL.
SQL Statements - Every SQL expression in the Queries, Forms, Reports and Modules of the Microsoft Access Database must then be converted from JET Syntax to T-SQL Syntax. This is by far the most difficult and challenging conversion component of any Access to SQL conversion project. This conversion component is usually at least 90% automated by 2SQL.
VBA Functions – SQL Statements of Access Query Objects/Form and Report Controls, Table Column Default Values and Table Column Validation Rules can refer to VBA Functions, most of which have a SQL Server equivalent, but require syntax changes in order to function correctly. Others are completely foreign to SQL Server and these require special User Defined Function equivalents. An awareness of these functions and their equivalents is needed in order to convert the SQL Statements from JET Syntax to T-SQL Syntax. This conversion component is usually at least 95% automated by 2SQL.
SQL Server Objects – The converted SQL Statements will reside in the SQL Server database as Views or Stored Procedures. In addition, and as a result of conversion requirements to achieve identical functionality, there can also be User Defined Functions, Foreign Key Constraints, Check Constraints, and Triggers. This conversion component is usually at least 95% automated by 2SQL.
ODBC Connection Objects - Each table or query object that was originally in the Microsoft Access Database must be replaced with an ODBC link connecting to the corresponding SQL Server object. This conversion component is usually 100% automated by 2SQL.
Application Framework – The properties and controls of Forms and Reports that contain SQL Statements will require modification to refer to the corresponding SQL Server Views and Stored Procedures. In addition, properties such as Filter and OrderBy can drag down performance and if so, they will require an equivalent and optimised alternative solution. This conversion component is usually 100% automated by 2SQL.
Optimization of Data Objects – DAO Recordsets, Executes and DBEngine commands are JET based which means that client side processing occurs when this programming library is used. Domain functions such as DLOOKUP are also client side based in their processing. Left alone, this will compromise performance and defeat the purpose of upsizing to SQL Server. To make these commands and functions server side based in their processing they need converting to an ADO/OLE based Connection property, with associated SQL Statements converted from JET to T-SQL. This conversion component is usually 100% automated by 2SQL except for SQL Statements, which currently need to be converted manually.
VBA Framework – With much of the software residing in SQL Server after the conversion, some of the commands in the VBA code will also require conversion to function correctly. For example, calls to DoCmd.OpenQuery that originally referred to SELECT query objects, must be converted to DoCmd.OpenTable. This conversion component is usually 100% automated by 2SQL.
User Acceptance Testing – Once the development phase of the conversion project is complete, it will be time to test the entire application with users who have an intimate understanding of how the application is supposed to work. This conversion component is out of scope for 2SQL, but the 2SQL Audit Database of results found and logged during the 2SQL conversion session can be an invaluable resource in troubleshooting any issues found.
Deploy to Production Environment – Upon the completion of User Acceptance Testing, it will be time to roll out the Access Front End and SQL Server Back End to the production environment. This requires migrating the latest set of data once more from Access to SQL Server, and then synchronising the tables that hold this data to all the other SQL Server objects that were created during the conversion project. This conversion component is usually 100% automated by 2SQL.
For the first nine components (Table Schema through to VBA Framework), there are specific “conversion issues” all of which must be addressed to ensure a complete and stable conversion. 2SQL identifies all of the issues and automatically remedies them. For a detailed analysis of the Conversion Categories, visit the Empirical Case Studies Page.
Some manual “clean up” will be required to fully convert the database. This amount will vary depending on the complexity of the application. CU2 Global provides documentation and 2SQL provides reports and built-in utilities which streamline the clean up process. Most of the clean up work will be converting SQL Statements in the VBA code from JET syntax to T-SQL Syntax, which is part of the conversion component above named Optimization of Data Objects.