Equivalent ANSI SQL Data Types - Access

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

Equivalent ANSI SQL Data Types

Applies to: Microsoft Office Access 2003

The following table lists ANSI SQL data types, their equivalent Microsoft Jet database engine SQL data types, and their valid synonyms. It also lists the equivalent Microsoft® SQL Server™ data types.

ANSI SQL data type	Microsoft Jet 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 Jet SQL BIT data type. It corresponds to the BINARY data type instead. There is no ANSI SQL equivalent for the Microsoft Jet 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 representation format .
If the data type name TEXT is used without specifying the optional length, for example TEXT(25), 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 Jet applications, while enabling the TEXT data type (without a length specification) to be aligned with Microsoft SQL Server.

See Also