Export (0) Print
Expand All
5 out of 11 rated this helpful - Rate this topic

Equivalent ANSI SQL Data Types

Office 2007

The following table lists ANSI SQL 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 typeMicrosoft Access SQL data type SynonymMicrosoft SQL Server data type
BIT, BIT VARYINGBINARY (See Notes)VARBINARY, BINARY VARYING BIT VARYINGBINARY, VARBINARY
Not supportedBIT (See Notes)BOOLEAN, LOGICAL, LOGICAL1, YESNOBIT
Not supportedTINYINTINTEGER1, BYTETINYINT
Not supportedCOUNTER (See Notes)AUTOINCREMENT(See Notes)
Not supportedMONEYCURRENCYMONEY
DATE, TIME, TIMESTAMPDATETIMEDATE, TIME  (See Notes)DATETIME
Not supportedUNIQUEIDENTIFIERGUIDUNIQUEIDENTIFIER
DECIMALDECIMALNUMERIC, DECDECIMAL
REALREALSINGLE, FLOAT4, IEEESINGLEREAL
DOUBLE PRECISION, FLOATFLOATDOUBLE, FLOAT8, IEEEDOUBLE, NUMBER (See Notes)FLOAT
SMALLINTSMALLINTSHORT, INTEGER2SMALLINT
INTEGERINTEGERLONG, INT, INTEGER4INTEGER
INTERVALNot supportedNot supported
Not supportedIMAGELONGBINARY,  GENERAL, OLEOBJECTIMAGE
Not supportedTEXT  (See Notes)LONGTEXT, LONGCHAR, MEMO, NOTE, NTEXT (See Notes)TEXT
CHARACTER, CHARACTER VARYING, NATIONAL CHARACTER, NATIONAL CHARACTER VARYINGCHAR (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
Bb177899.vs_note(en-us,office.12).gif  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 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

Microsoft Access Database Engine SQL Data Types



Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.