MS-Access / Getting Started

---

Planning for Upsizing

In this section, you will look at how to plan for upsizing an Access database to SQL Server. This includes looking at how the available data types in SQL Server can be matched with those in Access, and the steps that you need to take to verify that your Access database can be correctly converted. This verification will save you a lot of time, regardless of what tool you choose to perform the conversion.

Text Data Types and UNICODE

The ASCII character encoding scheme has been in use for a long time, but is has an inherent restriction because it supports only 255 different characters in its code set. UNICODE, however, supports over 65,000 characters. The SQL Server data types for character data are CHAR, VARCHAR, and an older TEXT data type, which is equivalent to the Access Memo data type. CHAR is a fixed size and VARCHAR is varying size, so the Access Text data type matches the SQL Server VARCHAR data type.

The SQL Server UNICODE data types can be identified by the "N" prefix, such as NCHAR, NVARCHAR and NTEXT. If you choose to use UNICODE, this will double the required storage, which could have an impact on performance, but you gain a greater flexibility and support for international deployment of your application.

SQL Server has an 8 K page-the maximum column size is 8,000 bytes, and the maximum row size is 8,060 bytes (1 row per page). But SQL Server 2008 R2 does allow the combined length of character and certain other data types to exceed this maximum row size. When dealing with a Memo data type, you have the option of mapping this to an older TEXT or NTEXT data type, or, beginning with SQL Server 2005, you can use VARCHAR(MAX) or NVARCHAR(MAX) data type, which is a better choice. The VARCHAR(MAX) data type can hold up to two gigabytes of data, and is stored in-row, when possible, to improve performance.

Date and Time Data

The Access Date data type can hold dates from 1 January, 100 to 31 December, 9999. When no date is given and time-only data is provided, it uses the date 30 December, 1899 to indicate time-only data and hides the date information.

The corresponding data type in SQL Server is the DATETIME data type. This can hold dates from 1 January, 1753 to 31 December, 9999. When no date is provided, it defaults the date to 1 January, 1900 (which you might have spotted is different in Access). SQL Server also has a SMALLDATETIME data type.

Beginning with SQL Server 2008, there are some new date and time data types, they are: DATE, TIME, DATETIME2 and DATETIMEOFFSET. When you link Access to these data types, the DATE data type will appear as an Access Date data type, and can store dates in the range 1 January, 0000 to 31 December, 9999.

The most common choice of mapping is to map the Access Date data type to the DATETIME data type, but if you only had date data you could consider using the newer DATE data type.

If you use a map to the DATETIME data type, then because it is possible for your Access tables to contain badly entered dates-for example in the year 100-before you convert your database, you should verify that your date data is no earlier than 1 January, 1753 (this is one of the most common problems that applications can have and can cause data conversions to fail). If you find bad dates, and if the field is not a required field, you can set the value to NULL. If it is a required date, set the field to a safe value. The following VBA code in the Northwind_ProblemsAndFixes.accdb can be used to search through all the dates in your database, looking for bad dates and making corrections:

Sub modUpsizeTests_CheckDatesAndFix()
' Search through all tables with date fields
' Check that there are not tables containing dates
' which will not be accepted in SQL Server
' set illegal dates to NULL or a SafeDate
    Const strMinDate = "1 January 1753"
    Const strSafeDate = "1 January 1900"
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim tdef As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    Dim strCriteria As String
    Set db = CurrentDb
    For Each tdef In db.TableDefs
	If Left(tdef.Name, 4) <> "MSys" And _
	    Left(tdef.Name, 4) <> "USys" And _
	    Left(tdef.Name, 4) <> "~TMP" Then
	    For Each fld In tdef.Fields
		If fld.Type = dbDate Then
		    strCriteria = "WHERE " & _
			"[" & fld.Name & "] < #" & _
			strMinDate & "#"
		    strSQL = "SELECT Count(*) FROM " & _
			"[" & tdef.Name & "] " & strCriteria
		    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
		    If rst(0) <> 0 Then
			Debug.Print tdef.Name, fld.Name
			If Not fld.Required Then
			    strSQL = "UPDATE [" & tdef.Name & "]" & _
				    " SET [" & fld.Name & "] = NULL " & _
				    strCriteria
			Else
			    strSQL = "UPDATE [" & tdef.Name & "]" & _
				    " SET [" & fld.Name & "] = #" & _
				    strSafeDate & "# " & strCriteria
			End If
			CurrentDb.Execute strSQL
		    End If
		    rst.Close
		End If
	    Next
	End If
    Next
End Sub

With TIME-only data, you need to be aware that as long as Access is used to continue entering this date, the default 30 December 1899 will be applied. But if you decide to write T-SQL in SQL Server, then ensure that you explicitly supply this date, because the default in SQL Server is 1 January 1900.