Field type names (JET, DDL, DAO and ADOX)

        10 votes: *****      20,671 views      0 comments
by Allen Browne, 20 April 2005    (Access 2000+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne, August 2004. Updated February 2008.


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

You can create and manage tables in Access using:

  • the interface (table design view);
  • Data Definition Language (DDL) query statements;
  • DAO code;
  • ADOX code.

Each approach uses different names for the same field types. This reference provides a comparison.

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

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Field type names (JET, DDL, DAO and ADOX)'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.