Upsizing Wizard is used to migrate the tables present in an access database to SQL server DataBase.It is found on the Access menu at Tools –> Database Utilities–> Upsizing Wizard.
Although the upsizing wizard can handle many of the differences between sql and access and convert objects correctly, but it can’t handle all the differences.
Before upsizing a access database to SQL Server, there are several important points we should take into consideration. The following points describe the most common issues.Issues involved while using upsize wizard
1.The below Access SQL syntax elements are not supported and hence are not converted.
DROP INDEX ,DISTINCTROW ,OWNERACCESS ,Table in UNION ,ORDER BY in Unions ,TRANSFORM ,PARAMETERS
2.If access table does not have an index for identifying the rows uniquely, then the upsizing wizard create the table but it will be read only in sql.
3.For the DefaultValue property property, upsize wizard tries to map with the visual basic functions.
if it fails to map the function for the field’s default value property, then the entire table is not get created.4.If we have apostrophes in the validation text, then those are get displayed as quotation marks when upsized.
5.If we have any caption set for any column in access table, then that was ignored by the upsizing tool.
6.AllowZeroLength property set for a column is not get upsized, It does not create any constraint for this rule for the upsized table in sql.
7.Some of the queries are not get upsized by upsize wizard.The following are the list of queries that are not get upsized.
Crosstab queries,Action queries (append, delete, make-table, update) that take parameters, Action queries that contain nested queries ,SQL pass-through queries ,SQL Data Definition Language (DDL) queries ,Union queries ,Queries that reference values on a form.
8.If a field is set as autoincremented field in access, then after upsizing it will not get created as Indentity Specification=”yes” in sql server.
9.If any default value is set for any field, then after upsizing that will not get created in sql server.
10.some functions like Datediff works in Access but not in SQL Server.
11.Tables Not Upsized When Query or Table Name Contains Apostrophe.
12. In access the date range is in between “1st Jan 100 to 31st Dec 9999” and in sql the date range is in between “1st Jan 1753 to 31st Dec 9999”.So if a access table conatin any date that is not supported by the SQL, then the table is not get upsized.