Provided by Allen Browne, August 2004. Updated April 2010.
You can create and manage tables in Access using:
Each approach uses different names for the same field types. This reference provides a comparison.
For calculated fields, Access 2010 reports the data type specified in the ResultType propety.
For code to convert the DAO number into a field type name, see FieldTypeName().
| JET (Interface) | DDL (Queries) [1] | DAO constant / decimal / hex [2] | ADOX constant / decimal / hex | ||||
| Text [3] | TEXT (size) [4] | dbText | 10 | A | adVarWChar | 202 | CA |
| dbComplexText | 109 | 6D | |||||
| [5] | CHAR (size) | dbText [6] | 10 | A | adWChar | 130 | 82 |
| Memo | MEMO | dbMemo | 12 | C | adLongVarWChar | 203 | CB |
| Number: Byte | BYTE | dbByte | 2 | 2 | adUnsignedTinyInt | 17 | 11 |
| dbComplexByte | 102 | 66 | |||||
| Number: Integer | SHORT | dbInteger | 3 | 3 | adSmallInt | 2 | 2 |
| dbComplexInteger | 103 | 67 | |||||
| Number: Long | LONG | dbLong | 4 | 4 | adInteger | 3 | 3 |
| dbComplexLong | 104 | 68 | |||||
| Number: Single | SINGLE | dbSingle | 6 | 6 | adSingle | 4 | 4 |
| dbComplexSingle | 105 | 69 | |||||
| Number: Double | DOUBLE | dbDouble | 7 | 7 | adDouble | 5 | 5 |
| dbComplexDouble | 106 | 6A | |||||
| Number: Replica | GUID | dbGUID | 15 | F | adGUID | 72 | 48 |
| dbComplexGUID | 107 | 6B | |||||
| Number: Decimal | DECIMAL (precision, scale) [7] | dbDecimal | 20 | 14 | adNumeric | 131 | 83 |
| dbComplexDecimal | 108 | 6C | |||||
| Date/Time | DATETIME | dbDate | 8 | 8 | adDate | 7 | 7 |
| Currency | CURRENCY | dbCurrency | 5 | 5 | adCurrency | 6 | 6 |
| Auto Number | COUNTER (seed, increment) [8] | dbLong with attributes | 4 | 4 | adInteger with attributes | 3 | 3 |
| Yes/No | YESNO | dbBoolean | 1 | 1 | adBoolean | 11 | B |
| OLE Object | LONGBINARY | dbLongBinary | 11 | B | adLongVarBinary | 205 | CD |
| Hyperlink | [9] | dbMemo with attributes | 12 | C | adLongVarWChar with attributes | 203 | CB |
| Attachment | dbAttachment | 101 | 65 | ||||
| [10] | BINARY (size) | dbBinary | 9 | 9 | adVarBinary | 204 | CC |
[1] There are numerous synonyms for these names, such as DATE for DATETIME, SHORT for INTEGER, FLOAT for DOUBLE, LONGTEXT for MEMO, MONEY for CURRENCY. Some synonyms work in limited contexts, e.g. ADO only.
[2] In attached tables, JET can recognise fields of type dbChar, dbFloat, dbNumeric, dbTime, dbTimeStamp, and dbVarBinary, but you cannot create fields of this type in an Access database.
[3] The complex data types require Access 2007 or later, and cannot be created with DDL or ADOX. In the interface, click the Lookup tab in the lower pane of table design, set Display Control to Combo Box, and Allow Multiple Values to Yes. (Alternatively, use the Lookup Wizard.)
[4] Include the brackets if you specify a size (number of characters). If you do not specify a size, the field type depends how you execute the DDL query: under DAO you get a Text field (dbText/adVarWChar), but under ADO/ADOX you get a Memo field (dbMemo/adLongVarWChar).
[5] You cannot create a fixed-width Text field through the interface.
[6] To create a fixed width field with DAO, set the field’s Attributes to: Attributes Or dbFixedField.
[7] Not available in the Access query interface or DAO. Use ADO to Execute the DDL query statement.
[8] The initial seed value and increment value are optional, and can be used only with ADO.
[9] The Hyperlink type field cannot be created with DDL.
[10] The fixed-width binary type field cannot be created with the user-interface.
| Home | Index of tips | Top |