MS-Access / Getting Started

---

Boolean Data

An Access Yes/No data type corresponds to the SQL Server BIT data type. In Access TRUE is -1 and FALSE is 0; in SQL Server, TRUE is 1 and FALSE is 0.

In Access, if you don't provide a default value for a Yes/No field, it will be always be displayed as FALSE. In SQL Server, if you don't specify a default, the default value is NULL. This means that unlike Access, a SQL Server BIT (Yes/No) field can be NULL, TRUE or FALSE.

Access, when linked to this data type, displays NULL as FALSE. The best advice is to check all your Yes/No fields to verify that they have a default value so that once converted to SQL Server, they have defaults when new data is entered. And in future, when creating new BIT fields in SQL Server, always give them a default value. The reason that this is very important is because SQL Server does not treat NULL as FALSE; it treats NULL as NULL, and testing for FALSE does not identify any rows containing NULL values. If you ignore this point, then at some point when you convert an Access query to a SQL Server View, if that query tests for FALSE, you will find that it no longer works as expected (during conversion you need to watch that FALSE becomes 0 and that -1 or TRUE becomes 1 or <>0). The following code sample could be used to add missing defaults to Yes/No fields prior to converting your database:

Sub modUpsizeTests_CheckBooleansForMissingDefaults()
' Search through all tables with Yes/No fields
' Add any missing defaults

    Dim db As DAO.Database
    Dim tdef As DAO.TableDef
    Dim fld As DAO.Field
    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 = dbBoolean Then
		    If fld.DefaultValue = "" Then
			Debug.Print tdef.Name, fld.Name
			' Set default to false
			fld.DefaultValue = "0"
		    End If
		End If
	    Next
	End If
    Next
End Sub

Integer Numbers

SQL Server integers range as follows (the Access equivalent types are shown in parentheses): TINYINT (Byte), SMALLINT (Integer), INT (Long Integer), BIGINT (no equivalent). If you define a BIGINT in SQL Server and use a linked table to Access, the field appears as a Text field.

Real Numbers, Decimals, and Floating-Point Numbers

SQL Server can hold very large or very small numbers by using the FLOAT data type. The type REAL (Access equivalent, Single) is equivalent to a FLOAT(24), 24-byte floating-point number. The default FLOAT is a FLOAT(53), which is equivalent to an Access Double. SQL Server also supports the DECIMAL data type, which aligns with the Access Decimal data type.

Hyperlinks

SQL Server has no equivalent to the Access Hyperlink data type, which you would translate to a VARCHAR or NVARCHAR data type. You would then require some code in Access to make it behave as a hyperlink, using an unbound hyperlink control. Access stores the hyperlink in a memo type column with the Address, Text, and ScreenTip information.