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
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.
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.
In this tutorial:
- Upsizing Access to SQL Server
- Planning for Upsizing
- Boolean Data
- IMAGE, VARBINARY(Max), and OLE Data
- Cycles and Multiple Cascade Paths
- Replicated Databases and Random Autonumbers
- Unique Index and Ignore Nulls
- Timestamps and Row Versioning
- Schemas and Synonyms
- Upsizing Wizard and the SQL Server Migration Assistant
- Upsizing to Use an Access Data Project
- Comparing Table Conversion in the Upsizing Wizard and SSMA
- Comparing Query Conversion in the Upsizing Wizard and SSMA
- Developing with Access and SQL Server
- Stored Procedures and Temporary Tables
- Handling Complex Queries
- Performance and Execution Plans
- SQL Server Profiler