The Microsoft Jet database engine recognizes several overlapping sets of data types. In Microsoft Access, there are four different contexts in which you may need to specify a data type — in table Design view, in the Query Parameters dialog box, in Visual Basic, and in SQL view in a query.
The following table compares the five sets of data types that correspond to each context. The first column lists the Type property settings available in table Design view and the five FieldSize property settings for the Number data type. The second column lists the corresponding query parameter data types available for designing parameter queries in the Query Parameters dialog box. The third column lists the corresponding Visual Basic data types. The fourth column lists DAO Field object data types. The fifth column lists the corresponding Jet database engine SQL data types defined by the Jet database engine along with their valid synonyms.
| Table fields | Query parameters | Visual Basic | ADO Data Type property constants | Microsoft Jet database engine SQL and synonyms |
|---|---|---|---|---|
| Not supported | Binary | Not supported | adBinary | BINARY (See Notes) (Synonym: VARBINARY) |
| Yes/No | Yes/No | Boolean | adBoolean | BOOLEAN (Synonyms: BIT, LOGICAL, LOGICAL1, YESNO) |
| Number (FieldSize = Byte) |
Byte | Byte | adUnsignedTinyInt | BYTE (Synonym: INTEGER1) |
| AutoNumber (FieldSize= Long Integer) |
Long Integer | Long | adInteger | COUNTER (Synonym: AUTOINCREMENT) |
| Currency | Currency | Currency | adCurrency | CURRENCY (Synonym: MONEY) |
| Date/Time | Date/Time | Date | adDate | DATETIME (Synonyms: DATE, TIME, TIMESTAMP) |
| Number (FieldSize = Double) |
Double | Double | adDouble | DOUBLE (Synonyms: FLOAT, FLOAT8, IEEEDOUBLE, NUMBER, NUMERIC) |
| AutoNumber /GUID
(FieldSize = |
Replication ID | Not supported | adGUID | GUID |
| Number (FieldSize = Long Integer) |
Long Integer | Long | adInteger | LONG (See Notes) (Synonyms: INT, INTEGER, INTEGER4) |
| OLE Object | OLE Object | String | adLongVarBinary | LONGBINARY (Synonyms: GENERAL, OLEOBJECT) |
| Memo | Memo | String | adLongVarWChar | LONGTEXT (Synonyms: LONGCHAR, MEMO, NOTE) |
| Number (FieldSize = Single) |
Single | Single | adSingle | SINGLE (Synonyms: FLOAT4, IEEESINGLE, REAL) |
| Number (FieldSize = Integer) |
Integer | Integer | adSmallInt | SHORT (See Notes) (Synonyms: INTEGER2, SMALLINT) |
| Text | Text | String | adVarWChar | TEXT (Synonyms: ALPHANUMERIC, CHAR, CHARACTER, STRING, VARCHAR) |
| Hyperlink | Memo | String | adLongVarWChar | LONGTEXT (Synonyms: LONGCHAR, MEMO, NOTE) |
| Not supported | Value | Variant | adVariant | VALUE (See Notes) |
Notes