Applies to:	Access 2000+
User Level:	Easy
Views:	9,012
Rating:	(7 votes)
Comments:	0 Have your say...

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
Text [3]		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
Number: Byte		dbComplexByte	102	66
Number: Integer	SHORT	dbInteger	3	3	adSmallInt	2	2
Number: Integer		dbComplexInteger	103	67
Number: Long	LONG	dbLong	4	4	adInteger	3	3
Number: Long		dbComplexLong	104	68
Number: Single	SINGLE	dbSingle	6	6	adSingle	4	4
Number: Single		dbComplexSingle	105	69
Number: Double	DOUBLE	dbDouble	7	7	adDouble	5	5
Number: Double		dbComplexDouble	106	6A
Number: Replica	GUID	dbGUID	15	F	adGUID	72	48
Number: Replica		dbComplexGUID	107	6B
Number: Decimal	DECIMAL (precision, scale) [7]	dbDecimal	20	14	adNumeric	131	83
Number: Decimal		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

Please Note: This is a cached tutorial (reproduced with permission).
To view the tutorial at its original location click here.

Other articles that may interest you:

Validation Rules
Undelete Tables and Queries in Access
Creating an Audit Trail (Source Code)

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

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...

Site tools

SmartAccess
300+ articles.
Promo code EA-45K2D46TVS