Access to SQL Server Migration: Understanding Data Type Conversions

By Mary Chipman

Overview

In this blog post I’ll discuss how you can use the SSMA Migration Assistant for Access (SSMA) to refine data type mappings between Access and SQL Server, but first a little history lesson. You can think of Access as two separate products rolled into one: the relational database engine, Jet, consisting of tables and queries, and Access application objects, consisting of forms, reports, macros and modules. Access 2007 introduced the Access Connectivity Engine (ACE), replacing Jet. It is fully backwards compatible with earlier versions of Jet, but incorporates new data types and functionality for integrating with SharePoint. Access 2010 completes the SharePoint "vision", making Access a first-class client application for SharePoint. What this means is that little work was done on Access-SQL Server integration, so much of the information available online for data type mappings between Access 2007 and SQL Server 2005 is still valid. Many of the new types introduced in SQL Server 2005 and 2008 are not supported in any version of Access, and the new SharePoint-compatible types introduced in Access 2007 and 2010 are not supported in SQL Server. Hopefully by the end of this article you’ll be able to find the information you need to manage data type conversions between your Access/Jet/ACE application and SQL Server.

Mapping data types

SSMA can help you customize the way data types are converted from Access to SQL Server. Follow these steps to change the default data type mappings.

1. Open your SSMA project.
2. Choose Tools, Project Settings, Type Mapping from the menu. The default data type mappings for SSMA are displayed in the following screenshot.

1. Select the Access data type you want to map in the Source Type, and the SQL Server Target Type, and click Edit. This will allow you to edit the type mapping, as shown in the following screenshot. Click OK when finished.

The following table displays the names of the Access data type names as they appear in the Access UI, and their default SQL Server data type mappings.

To research the best SQL Server data type mappings for your application, see Data Types (Database Engine) https://msdn.microsoft.com/en-us/library/ms187594(v=SQL.105).aspx Data Types (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms187752.aspx in SQL Server Books Online.

Working with incompatible data types

If your tables have replication columns (Replication ID), you should remove them before migrating your data. After your data has been migrated to SQL Server, there are a variety of replication and synchronization technologies to choose from. For more information, see SQL Server Replication https://msdn.microsoft.com/en-us/library/ms151198.aspx.

The Attachment data type and multi-valued fields are not supported in SQL Server. Multi-valued fields are for compatibility with SharePoint, and the Attachment data type lets you store binary files in an Access database. Instead of attachments, you might want to consider FILESTREAM storage. For more information, see FILESTREAM Storage in SQL Server 2008 https://msdn.microsoft.com/en-us/library/cc949109.aspx.

Working with SQL Server datetime data ranges

The Access Date/Time data type supports a range of values from 1/1/100 through 12/31/9999 whereas the SQL Server datetime data type is limited in range to values from 1/1/1753 through 12/31/9999. SSMA will flag values that fall outside of the SQL Server datetime range in an assessment report. Note that SSMA can convert Access datetime to SQL Server datetime2, which was introduced in SQL Server 2008. However, the new date and time data types are not supported in Access if you use Access as a front end after converting your data. For more information, see Using Date and Time Data https://msdn.microsoft.com/en-us/library/ms180878(v=SQL.105).aspx in SQL Server Books Online.

Note Access and SQL Server store datetime values differently. If your application contains custom logic based on assumptions about how datetime values are stored as numbers, that logic will fail after your data has been migrated to SQL Server.

Dealing with Unicode (nchar, nvarchar, ntext)

Depending on the needs of your application, you may want to consider revising the default mapping for Access Text data from nvarchar to varchar. Varchar takes one byte of storage, but is limited to 265 characters. The Unicode encoding of nvarchar uses 2 bytes to encode each character, and is capable of handling non-European alphabets. If your application does not require extended character sets, nvarchar may be overkill. For more information, see Using Unicode Data https://msdn.microsoft.com/en-us/library/ms191200.aspx and Working with Unicode Data https://msdn.microsoft.com/en-us/library/ms175180.aspx in SQL Server Books Online.

Managing null values, zero-length strings, and three-valued logic

Both Access and SQL Server support the concept of nulls and zero-length strings, and there is often confusion and controversy about how nulls should be used in database applications. In three-valued logic, a comparison can evaluate to one of three conditions:

• True
• False
• Unknown (null)

In SQL Server, null is considered to be unknown. Two null values compared to each other are not considered to be equal—they are both unknown, so it is impossible to determine if they would be equal if their values were known. In expressions using arithmetic operators, if any of the operands is null, the result is null as well. Microsoft recommends that you avoid using null values in your tables because it adds complexity to queries and updates. For more information, see Allowing Null Values https://msdn.microsoft.com/en-us/library/ms189265(v=SQL.105).aspx in SQL Server Books Online.

Handling Booleans, bits, and True

In Access, Yes/No (Boolean) values are stored using the same values for Boolean variables in VBA, 0 and -1. The bit data type in SQL Server uses 0 and 1. If you use the default SSMA mapping to a SQL Server bit column, any code that relies on True being represented by the numeric value -1 will encounter logic errors. You have two options: migrate to a smallint column, or fix the logic that depends on True being -1. If your logic uses False (zero), you can migrate to the more efficient tinyint column in SQL Server. The value ranges for integer data types are discussed in Using Integer Data https://msdn.microsoft.com/en-us/library/ms189324.aspx in SQL Server Books Online.

Solving issues with hyperlinks

The Access Hyperlink data type stores three pieces of data¾the URL, display text, and a screen tip. This data is stored in a Memo column delimited by pound signs, with a special attribute indicating that the column is a hyperlink. Hyperlinks only work in the Access UI with native Jet or ACE tables, not with SQL Server data.

The Access user interface detects these columns and handles them differently from regular Memo fields. However, when you migrate the data to SQL Server, the special attribute is gone and all that remains is the pound-sign-delimited data. You will need to research alternative solutions in your client application if you need to duplicate the behavior of Hyperlinks in your client application.

Additional Resources and References

Successfully migrating your data often depends on your ability to locate information that is specific to your environment, which often challenging as neither the Access nor the SQL Server documentation (SQL Server Books Online) is consistent from version to version. In SQL Server Books Online, topics have been moved, reorganized and renamed with each product version, and the Jet database engine is essentially undocumented since version 3.5. When accessing information on Microsoft’s web sites, make sure that the page you are viewing is the correct one for the version of the software you are using.

The Wikipedia article, Microsoft Jet Database Engine, https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine, has more information than you’ll probably ever need, but it does clarify Jet’s convoluted history and how it came to be synonymous with Access.

Scroll down to Learn how Access interprets SQL Server data types in the topic Import or link to SQL Server data https://office.microsoft.com/en-us/access-help/import-or-link-to-sql-server-data-HA010200494.aspx on the Office site. This topic does not appear to have been updated for Access 2010.

This third-party paper, What are the main differences between Access and SQL Server? https://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html provides sample code and workarounds for many data conversion issues.

Joe Celko has written BIT of a Problem discussing some of the drawbacks with using the bit data type https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/. There are other articles on simple-talk that you may also find useful.

Bio

Mary Chipman is an independent consultant who has written and spoken extensively about Microsoft data platforms and technologies. She was awarded MVP status (Most Valuable Professional) from 1994 through 2004 when she joined Microsoft, working as a programmer/writer until 2010. She has authored and presented award-winning SQL Server and .NET courseware for Application Developers Training Company (AppDev.com) and spoken at industry conferences, most recently TechEd 2008 and 2009. She is co-author of the classic Microsoft Access Developer's Guide to SQL Server https://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446/.