2SQL v WIZARD and SSMA

The two free conversion tools available from Microsoft only take you part of the way through a conversion from Access to SQL Server - estimates vary with both the complexity of your databases and the outcome you would like to achieve, but the degree of success is likely to be in the range of 5% to 20% completion of the full project.  2SQL maximises server-side processing and minimises client-side processing, a capability that is very important when converting complex databases. 

The following table provides a brief summary of the 30 high level Conversion Categories, comparing the functionality of the Upsizing Wizard, the latest version (July 2008) of SSMA, and 2SQL. 

To explain the main headings in the table:

"Object Groups" represent a summary grouping of Conversion Categories, which in turn represent the (several hundred) types of conversion issues. "Rating" is the weighting we give each Conversion Category for the degree of difficulty for a Programmer to find and fix an issue. Ratings range from zero to eight, with zero being the common 'silver bullet' areas of functionality for all three of the Upsizing Wizard, SSMA and 2SQL, and eight being issues that can be extremely difficult to find, and usually very difficult to repair as well. By giving the more common categories a rating of zero, it follows that 2SQL also delivers that functionality without charge. A rating of zero can also occur if the Conversion Category is one that 2SQL does not repair, but will instead scan for the issue and log for manual review later.

indicates does not handle, P indicates partially handles, and means completely handles.  Due to the obvious number of grey areas, many of the gradings are our subjective estimates if the functionality is not clearly in a range of 90 to 100%.

     Expand All
     Collapse All

#	CONVERSION CATEGORY	COMPONENT	RATING	WIZARD	SSMA	2SQL
1.	Table Columns Without at least one column, there would not be a Table. If a Table only has one column or several hundred, conversion of Table columns to SQL Server is straightforward. This functionality is a silver bullet for the Wizard, SSMA, and 2SQL.	Table Schema/Data	0
2.	Integrity Checks Table Relationships in Access may have different field sizes in the source and foreign fields, but the equivalent Foreign Key constraint in SQL Server demands the same field size. Both the Wizard and SSMA report this issue, but do not repair.  2SQL has at least 98% functionality.  Null equates to Nothing in Access Jet, but in SQL Server Null has a value. This can prevent Primary or Unique indexes from being created. Invalid date values in fields of type Date will prevent all data in the Table from being migrated to SQL Server.  This is out of scope for both the Wizard and SSMA, but 2SQL will remove and audit invalid dates if there is a format property for the date field.  If there is no format property, 2SQL will log this as a design issue requiring manual review.	Table Schema/Data	0
3.	Column Indexes Columns of tables can have Primary, Unique, or Duplicate Row Indexes.  Conversion of column indexes to SQL Server is straightforward.  This functionality is a silver bullet for the Wizard, SSMA, and 2SQL.	Table Schema/Data	0	P
4.	Clustered Indexes Default Values of Table Columns must comply to T-SQL syntax during the conversion process.  The Wizard and SSMA functionality is proportional to complexity, but 2SQL has at least 98% functionality.	Table Schema/Data	0	P
5.	Column Default Values Default Values of Table Columns must comply to T-SQL syntax during the conversion process.  The Wizard and SSMA functionality is proportional to complexity, but 2SQL has at least 98% functionality.	Table Schema/Data	0	P
6.	Column Validation Rules Validation Rules of Table Columns must comply to T-SQL syntax during the conversion process.  The Wizard and SSMA functionality is proportional to complexity, but 2SQL has at least 98% functionality.	Table Schema/Data	0	P	P
7.	Table Relationships Foreign Key Constraints are the equivalent of Access Table Relationships in SQL Server. The Wizard and SSMA will convert table relationships to SQL Server, except as follows: 1.   If the source and foreign fields are the same size. 2.   If the constraint causes cyclical or multiple cascade paths. 2SQL addresses point one as part of the Schema Integrity Checks category above. Point 2 is reported by 2SQL and a solution is provided for manual implementation.	Table Schema/Data	1
8.	Data Migration The Wizard and SSMA will migrate Table data to SQL server except as follows:  1.   Date Fields containing invalid dates (both the Wizard and SSMA). 2.   Random AutoNumber keys (SSMA only).   2SQL handles both instances.	Table Schema/Data	0	P	P
9.	ANSI 89 Syntax Access Jet Syntax uses ANSI 89, and SQL Server uses ANSI 92, the conversion of which is out of scope for the Wizard.  SSMA has limited functionality proportional to complexity of the SQL Statements, but only for SELECT statements - for example, double quotes in SQL Statements must be converted to Single Quotes, and the Wildcard symbol * must be converted to %.  2SQL delivers to at least 95% conversion, irrespective of complexity.	SQL Statements	1		P
10.	String Concatenations The & symbol when used for concatenation of values is replaced with the plus (+) symbol.	SQL Statements	1
11.	Reserved Key Word Usage SQL Statements using Reserved Key Words must be encapsulated i.e. [Reserved Key Word], conversion of which is out of scope for both the Wizard and SSMA.  2SQL delivers to at least 95% conversion.	SQL Statements	1
12.	DISTINCTROW Selects DISTINCTROW SELECTS that DO refer to JOINS in the FROM clause, but also refer to one or more columns from each of the JOIN objects in the SELECT clause, are converted to normal SELECT statements.  Otherwise, DISTINCTROW is converted to DISTINCT.	SQL Statements	3
13.	Implicit Sorts Using Distinct Select Statements that use DISTINCT in Microsoft Access sort by the first SELECT column if there is no ORDER BY clause. 2SQL will determine the first ordinal positioned column of the SELECT clause and create an ORDER BY clause to sort on this column.	SQL Statements	2
14.	Duplicate Column Aliases Microsoft Access deals with Duplicate Column References in several different ways subject to the content of the SQL Statement, as follows: 1.     Ignores the reference completely so that it does not display and cannot be referred to by other SQL Statements. 2SQL will remove the duplicate reference. 2.     Creates an alias of value Expr101x on the fly, where x is a number from 0 to 9. 2SQL determines when this occurs and adds an alias of the same name to the duplicate column reference.  3.     When a column of the same name belongs to two (or more) objects, and both instances are referred to in the SELECT clause, Microsoft Access JET will dynamically name them as table1.columnname and table2.columnname, without aliases.  This has to be dynamic, because it is not possible to create columns with full stops in the column name. Although it is possible to create columns with full stops in SQL Server, they cannot be referred to from Microsoft Access by way of linked tables.  So 2SQL will create an alias of TableName_ColumnName in such instances, and it will also address the cascading effect this has on other SQL Statements that refer to them by way of nested queries.	SQL Statements	3
15.	Jet Syntax Access Jet Syntax requires conversion to SQL Server T-SQL, a function which is also out of scope for the Wizard.  SSMA has limited functionality proportional to complexity of SQL Statement, but only for SELECT statements - for example, logical expressions must be converted to CASE expressions.  2SQL delivers to at least 95% conversion, irrespective of complexity for ALL kinds of SQL Statements.	SQL Statements	2		P
16.	VBA Functions with SQL Server Equivalent Some VBA (Visual Basic for Applications) functions inside SQL Statements require no conversion, i.e. they work as is e.g. LEFT(.  Most, however, require conversion to their SQL Server equivalent. For example, TRIM requires conversion to LTRIM(RTRIM(.  These types of conversions are out of scope for the Wizard and SSMA has limited functionality.  2SQL has at least 95% functionality.	SQL Statements	2		P
17.	VBA Functions with no SQL Server Equivalent This is an extension of the category above - some VBA functions do not have a SQL Server Equivalent.  This is out of scope for both the Wizard and SSMA.  Conversion of VBA Functions without a SQL Server Equivalent is 2SQL working at its highest capability.  For example, 2SQL will generate a Table valued SQL Server Function for the VBA Partition function and modify all associated SQL Statements to call this function with the corresponding parameters.	SQL Statements	5
18.	References to Forms!Formname!Controlname Jet recognizes Form and Report controls in SQL Statements, but these are completely foreign to SQL Server T-SQL.  This conversion is out of scope for both the Wizard and SSMA.  Conversion of SQL Statements referring to Form and Report controls is 2SQL working at its highest capability, and the occurrence of these is far from uncommon.	SQL Statements	5
19.	Crosstab Queries Conversion of CrossTab SQL Statements is not part of either the Wizard or the SSMA functionality.  2SQL leverages the PIVOT command in T-SQL to provide a SQL Server Equivalent View or Stored Procedure.  This includes CrossTabs without an IN Clause.  Conversion of CrossTabs is 2SQL working at one of its highest capabilities.  On average, there are 3 CrossTabs in every 100 SQL Statements.	SQL Statements	8
20.	Optimisation of Data Objects There are 3 areas of VBA Code that require optimization:  1.     DAO RecordSets 2.     DAO Executes 3.     RunSQL  The connection properties of these objects and commands require conversion to the 2SQL ADO/OLE based 2SQL ADODirectConnection function.    2SQL will automatically convert the code syntax for these objects and commands, but not the SQL Statements that they use.	SQL Statements	0
21.	Domain Functions Domain Functions are converted to their equivalent 2SQL Toolkit function.  For example, DLOOKUP is converted to DLOOKUPDirect. 2SQL converts both VBA Code references, and also the controlsource property of Foms and Reports.	SQL Statements	5
22.	Code Alerts When 2SQL analyzes the VBA code, it will enforce compilation errors for lines of code that require manual intervention to complete the optimization or conversion process. For example, if an SQL Statement is used in an OpenRecordSet command as opposed to the name of a Table, 2SQL will generate a code alert so that the code will no longer compile until the SQL Statement is manually reviewed, remedied, and recompiled.	SQL Statements	0
23.	Append Queries Although almost identical in syntax, this functionality is out of scope for both the Wizard and SSMA.  2SQL has at least 98% functionality.	SQL Server Objects	1
24.	Update Queries Update Queries require conversion to SQL Server Stored Procedures.  The most common issue is the re-arrangement of the sequence of the SET Clause and the FROM clause.  This is out of scope for both the Wizard and SSMA, but 2SQL has at least 98% functionality.	SQL Server Objects	1
25.	Select and Union Queries These require conversion to SQL Server Views if there are no criteria parameters, or Stored Procedures if there are.  This out of scope for the Wizard, but SSMA will convert Select and Union Queries proportional to their complexity i.e. the more complex the issue, the less SSMA handles.  2SQL has at least 98% functionality.	SQL Server Objects	1		P
26.	Delete Queries Delete Queries require conversion to SQL Server Stored Procedures.  The most common issue is removal of references to table fields in the DELETE clause.  This is out of scope for both the Wizard and SSMA, but 2SQL has at least 98% functionality.	SQL Server Objects	1
27.	Delete Triggers SQL Statements with JOINS that are converted to Updateable Views cannot have rows deleted from the Microsoft Access Front End without an INSTEAD OF DELETE Trigger in SQL Server.  2SQL will calculate the primary table and unique columns of each View, and create the trigger.	SQL Server Objects	4
28.	Form and Report Properties/Controls The RecordSource and RowSource properties of Form and Report Controls can contain SQL Statements. Left alone, they will either malfunction or will perform poorly when processing large amounts of records.  To maximise performance and robustness, these SQL Statements should be converted to SQL Server Views or Stored Procedures. The values of the corresponding recordsource or rowsource property of the Form or Report should then be changed to the name of the SQL Server Object representing the original SQL Statement.  2SQL has at least 98% functionality, but this is out of scope for both the Wizard and SSMA.	SQL Server Objects	1
29.	User Defined Function Templates Access Jet will recognize VBA User Defined Functions for all types of SQL Statements (SELECT, APPEND etc), but these functions become totally foreign to the equivalent Views or Stored Procedures.  This is out of scope for both the Wizard and SSMA.  2SQL will create an equivalent SQL Server User Defined Function TEMPLATE, with the original VBA Code migrated as part of this template for manual conversion to T-SQL.	SQL Server Objects	3
30.	Virtual RowCount Reconciliation For every SQL Statement in the Access Queries, RecordSource of Forms and Reports, and RowSource property of Forms and Reports, 2SQL will execute the Statement in Access and Log the number of rows it returns.   When 2SQL creates the corresponding SQL Server View or Stored Procedure, it executes once more and compares the row count. If the original row count is greater than zero, and the SQL Server Row Count matches, this is logged as a successful rowcount reconciliation. If there is a row count conflict, an error is logged for review post conversion.	SQL Server Objects	1
31.	ODBC Links to Tables Tables in SQL Server require ODBC Links in the Access front end to allow communication to them from Forms, Reports, and VBA Code.   Conditional on the table being created in SQL Server, this functionality is a silver bullet for the Wizard, SSMA, and 2SQL.	ODBC Connection Objects	0
32.	ODBC Links to Views Each SQL Statement converted to a SQL Server View can become an ODBC table in the Access front end database. These views can then be referred to in the VBA code, and properties of Forms and Reports.  This conversion is out of scope for both the Wizard and SSMA, but 2SQL has at least 98% functionality.	ODBC Connection Objects	1
33.	ODBC Indexes By default, ODBC links to SQL Server views are not updateable. The original query however, may have been an updateable one and referred to in the VBA Code or the RecordSource property of Forms.  ODBC Indexes must be applied to the table object representing the SQL Server View in order to remain updateable.  This is out of scope for both the Wizard and SSMA.   2SQL has at least 95% functionality, and represents 2SQL working at its highest capability.	ODBC Connection Objects	2
34.	Pass-Through Queries Stored Procedures can be accessed from the Access front end by creating Pass-Through Query Links. If the stored procedure is one that returns records, these Pass-Through queries can then be referenced by the RecordSource property of Forms and Reports, or the RowSource property of Form and Report Controls.  2SQL has at least 98% functionality, but this is out of scope for both the Wizard and SSMA.	ODBC Connection Objects	1