The following table lists ANSI SQL (Structured Query Language (SQL): A database query and programming language widely used for accessing, querying, updating, and managing data in relational database systems.) data types, their equivalent Microsoft Access database engine SQL data types, and their valid synonyms. It also lists the equivalent Microsoft SQL Server data types.
ANSI SQL
data type |
Microsoft Access
SQL data type |
Synonym |
Microsoft SQL
Server data type |
| BIT, BIT VARYING |
BINARY (See Notes) |
VARBINARY,
BINARY VARYING
BIT VARYING |
BINARY, VARBINARY |
| Not supported |
BIT (See Notes) |
BOOLEAN, LOGICAL, LOGICAL1, YESNO |
BIT |
| Not supported |
TINYINT |
INTEGER1, BYTE |
TINYINT |
| Not supported |
COUNTER (See Notes) |
AUTOINCREMENT |
(See Notes) |
| Not supported |
MONEY |
CURRENCY |
MONEY |
| DATE, TIME, TIMESTAMP |
DATETIME |
DATE, TIME (See Notes) |
DATETIME |
| Not supported |
UNIQUEIDENTIFIER |
GUID |
UNIQUEIDENTIFIER |
| DECIMAL |
DECIMAL |
NUMERIC, DEC |
DECIMAL |
| REAL |
REAL |
SINGLE, FLOAT4, IEEESINGLE |
REAL |
| DOUBLE PRECISION, FLOAT |
FLOAT |
DOUBLE, FLOAT8, IEEEDOUBLE, NUMBER (See Notes) |
FLOAT |
| SMALLINT |
SMALLINT |
SHORT, INTEGER2 |
SMALLINT |
| INTEGER |
INTEGER |
LONG, INT, INTEGER4 |
INTEGER |
| INTERVAL |
Not supported |
|
Not supported |
| Not supported |
IMAGE |
LONGBINARY, GENERAL, OLEOBJECT |
IMAGE |
| Not supported |
TEXT (See Notes) |
LONGTEXT, LONGCHAR, MEMO, NOTE, NTEXT (See Notes) |
TEXT |
| CHARACTER, CHARACTER VARYING, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING |
CHAR (See Notes) |
TEXT(n), ALPHANUMERIC, CHARACTER, STRING, VARCHAR, CHARACTER VARYING, NCHAR, NATIONAL CHARACTER, NATIONAL CHAR, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING (See Notes) |
CHAR, VARCHAR, NCHAR, NVARCHAR |
Notes
- The ANSI SQL BIT data type does not correspond to the Microsoft Access SQL BIT data type. It corresponds to the BINARY data type instead. There is no ANSI SQL equivalent for the Microsoft Access SQL BIT data type.
- TIMESTAMP is no longer supported as a synonym for DATETIME.
- NUMERIC is no longer supported as a synonym for FLOAT or DOUBLE. NUMERIC is now used as a synonym for DECIMAL.
- A LONGTEXT field is always stored in the Unicode (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.) representation format.
- If the data type name TEXT is used without specifying the optional length, a LONGTEXT field is created. This enables CREATE TABLE statements to be written that will yield data types consistent with Microsoft SQL Server.
- A CHAR field is always stored in the Unicode representation format, which is the equivalent of the ANSI SQL NATIONAL CHAR data type.
- If the data type name TEXT is used and the optional length is specified, for example TEXT(25), the data type of the field is equivalent to the CHAR data type. This preserves backwards compatibility for most Microsoft Access applications, while enabling the TEXT data type (without a length specification) to be aligned with Microsoft SQL Server.
