Chapter 38: Upsizing Access Databases to SQL Server
1239
Although the Upsizing Wizard maps Access data types to SQL Server data types, there are other
conversion issues you need to be aware of. If the Upsizing Wizard Report indicates that a table has
been skipped, examine the field names in each of the Access tables to ensure that they adhere to
the following constraints:l (^) The first character must be a letter or the @ sign.
l The remaining characters may be numbers, letters, the dollar sign ($), the number sign
(#), or the underscore (_).
l Spaces are allowed, but the Upsizing Wizard will insert brackets ([ ]) around the field
name.
l The name must not be a Transact-SQL keyword. SQL Server reserves both the uppercase
and lowercase versions of keywords.
To verify SQL Server reserved words, go to http://msdn.microsoft.com, and search for
Transact-SQL Reference and SQL-Server Language Reference.
If any field name in an Access table fails to follow these guidelines, the Upsizing Wizard is not able
to upsize the table. The Upsizing Wizard Report informs you that the table has been skipped.
However, the wizard does not always provide the reason the table was skipped. When you review
the report, you can refer to this section to review the field-naming rules.
In addition to field-name constraints, the Upsizing Wizard also fails to upsize a table if it encoun-
ters any of these situations:
l The field size between two fields participating in an Access relationship is not exactly the
same for both fields.
l There is no unique index.
l (^) There is a unique index on a field and the Required property is set to No.
l More than two foreign keys are defined on a single table.
l (^) An Access database may contain data that are invalid in SQL Server. Date must be no ear-
lier than 1/1/1753 to be compatible with SQL Server. (Access supports a much wider
range of dates, all the way back to 1/1/100.)
After you’re finished reviewing the report, close it. When you close the report, the Upsizing
Wizard displays the modified Access application.
Note
You might notice that all Access text data types are upsized to SQL Server nvarchar columns. This may cause a
problem in some situations. The nvarchar data type supports Unicode (16-bit) character sets (as does Access),
which means every character requires 16 bits (2 bytes) of data storage, instead of 8 bits (1 byte). Upsizing very
large Access tables containing lots of text fields could, conceivably, overwhelm the 4GB limit on the SQL
Server Express database file. However, because an Access database is limited to 2GB, this is, at best, a remote
possibility.