Microsoft Access Tips for Serious Users

Provided by Allen Browne, August 2004. Updated April 2010.


Field type reference - names and values for DDL, DAO, and ADOX

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
Footnotes:

[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