The available data types are different in Microsoft® Access and Microsoft SQL Server™. SQL Server bases its data types on the C programming language; Access bases its data types on Microsoft Visual Basic®. The data types are converted as described in the following table.
| Access | SQL Server |
|---|---|
| Text | Varchar |
| Memo | Text |
| Byte | Smallint |
| Integer | Smallint |
| Long Integer | Int |
| Single | Real |
| Double | Float |
| Replication ID | Varbinary |
| Date/Time | Datetime |
| Currency | Money |
| Autonumber (Long Integer) | Int (Identity) |
| Yes/No | Bit |
| OLE Object | Image |
Be careful when selecting which data types to use in SQL Server. You can change a field’s data type in Access; however, it is not possible to change a data type in SQL Server. A new table must be created before the data can be transferred to that table.
The SQL Server timestamp data type has no counterpart in Access. In spite of its name, it is neither a time nor a date, nor is it some encoded representation of a time or date. A timestamp is a binary number column that is updated automatically every time a row is inserted or updated. This permits the client program to confirm whether values have changed since it last checked the record. A timestamp column enables BROWSE-mode updates and makes cursor update operations more efficient.
The timestamp field indicates only that a record was changed, not when it was changed. You cannot set the timestamp column to any specific value. To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to update the values automatically when any modification takes place.
SQL Server has an automatically incrementing field, called an Identity field, similar to the Access AutoNumber field. When migrating data from an AutoNumber field to an Identity field, turn off the functionality of the field with the IDENTITY_INSERT option, copy the existing AutoNumber values into the table, and then turn the IDENTITY_INSERT option back on. This preserves the original AutoNumber values from your Access table and begins future records with the highest number in your table, incrementing by one.
Access stores either a zero (0) or a negative one (-1) in the Yes/No data type. Access interprets 0 as 0, No, or False, and interprets any nonzero value as –1, Yes, or True.
A bit field stored in SQL Server that is selected in ISQL/w appears as either a zero (0) or a positive one (1), where 0 represents false and 1 represents true.
| Data Types | IDENTITY (Property) |
| timestamp |