April 30, 2013 in Microsoft Access
This page lists every possible field data type that a native Access (Access Database Engine) database can contain. It also shows which methods can be used to create fields of the given type. The information here applies to Access 2007+ (the ACE engine) but most information holds true for earlier versions as well.
List of Microsoft Access Data Types
Types of Types
Officially, there are 13 primary data types in Microsoft Access. See SQL Data Types. There a number of subtypes, however, depending on how an individual field is configured. Some data types add behaviors or formatting, and others allow multiple values to be stored in the same field. I group the available data types in Microsoft Access into three categories:
Base Data Types
These 16 data types define how data is fundamentally stored and retrieved. The MSDN documentation lists only 13 types because it lumps both short binary types into the BINARY type, and all three text types into the TEXT type. However, these types behave differently and are identified separately almost everywhere else so I split them out.
Extended Data Types
These 5 data types take an existing base data type and add a behavior to it. Two of the AutoNumber types take a normal 32 bit integer field and add an auto-populating behavior, while the AutoNumber GUID type does the same with a normal GUID field. The Hyperlink field is really just a memo (long text) field with a flag that the user interface uses to format the contents as a hyperlink. Finally, calculated fields can be of any non-binary type. They become “calculated” fields by assigning an expression. These steps are reversed in the table designer, where first you select the “Calculated” field type and then choose the “Result Type”.
Complex Data Types
Access 2007 introduced “complex” data types. Although often compared to multi-valued fields in other systems, they are more than that. Complex data types are essentially embedded recordsets. A single complex field can contain multiple records. Each record can contain one or more fields. For the complex number fields (ComplexInteger, ComplexSingle, etc), each record has only one field, the Value field. As a result these types are effectively lists. The attachment type, however, has multiple fields in each record: The file name, the file extension, the binary content of the file itself, and several other fields that are hidden in the user interface.
All text is UTF-16
A side note on text types: All text fields in Microsoft Access are Unicode (UTF-16) encoded. This means there are not separate types for ANSI and Unicode text; ANSI text is simply not available. Other platforms generally use the
nvarchar names for Unicode text (the ‘n’ is for ‘National’, an anachronistic reference to Unicode as a way to store multi-byte international alphabets) while
varchar indicate ANSI text. Both the
nchar forms work in Access DDL but they are just synonyms; they both create Unicode encoded text fields.
Unicode of course uses two bytes per character. This can be a waste of space for text composed only of characters that fit in the single-byte range. To address this, it is possible to compress text using the Unicode compression option. This is enabled via “Unicode Compression” in the table designer, via the
WITH COMPRESSION option in SQL, or via the “UnicodeCompression” extended property of the DAO Field object.
The Data Types
Here it is: The full list of distinct data types available in Microsoft Access!
|Data Type||SQL Data Type 1||GUI Name||Description|
|Base Data Types|
|Binary (Short, Fixed)||BINARY||Binary||Fixed-length binary data, up to 510 bytes|
|Binary (Short, Variable)||BINARY||Binary||Variable-length binary data, up to 510 bytes|
|Binary (Long)||IMAGE||OLE Object||Variable-length binary data, up to 2 GB|
|Boolean||BIT||Yes/No||True or false|
|Byte||TINYINT||Byte||Unsigned 8-bit integer|
|Currency||MONEY||Currency||8-byte scaled integer +/-922,337,203,685,477.5808.|
|DateTime||DATETIME||Date/Time||A datetime value with a one second resolution|
|GUID||UNIQUEIDENTIFIER||Replication ID||A GUID|
|Decimal||DECIMAL||Decimal||Scaled decimal field with up to 28 digits of precision|
|Single||REAL||Single||Single-precision (4-byte) floating point number|
|Double||FLOAT||Double||Double-precision (8-byte) floating point number|
|Int16||SMALLINT||Integer||16 bit signed integer|
|Int32||INTEGER||Long Integer||32 bit signed integer|
|Text (Short, Fixed)||TEXT||Text||Fixed-length text, up to 255 characters|
|Text (Short, Variable)||TEXT||Text||Variable-length text, up to 255 characters|
|Text (Long)||TEXT||Memo||Variable-length text, up to 2 GB|
|Extended Data Types|
|AutoNumber (Int32, Increment)||INTEGER||AutoNumber||Automatically populated integer, from a seed and increment|
|AutoNumber (Int32, Random)||INTEGER||AutoNumber||Automatically populated random integer|
|AutoNumber (GUID)||UNIQUEIDENTIFIER||AutoNumber||Automatically populated random GUID||Hyperlink||TEXT||Hyperlink||Variable text field presented as a hyperlink||Calculated||[varies]||Calculated||A field whose value is calculated from an expression|
|Complex Data Types|
|Attachment||A list of embedded files|
|Multivalue Byte||A list of Byte|
|Multivalue Decimal||A list of Decimal|
|Multivalue Double||A list of Double|
|Multivalue GUID||A list of GUID|
|Multivalue Int16||A list of Int16|
|Multivalue Int32||A list of Int32|
|Multivalue Single||A list of Single|
|Multivalue Text||A list of variable-length text, up to 255 characters|
1 From the 13 primary data types listed in SQL Data Types
Examining Data Types
Both the DAO object model and the ADOX object model provide ways of examining properties of a field in a table or query definition. The ADO method OpenSchema can also be used to fetch information about database objects and will return the same information as the ADOX object model.
In DAO, fields are represented by the Field object. DAO uses the same object for fields in the definition of a table, query, or other object as for data fields in a recordset. In ADOX, the Column object contains information only about the definition of fields in tables, queries, and other objects. It is not used for data fields in a recordset.
The DAO Field.Type property and the ADOX Column.Type property are the primary means of determining the data type of a field. The values of these properties correspond to constants in the DAO.DataTypeEnum and ADO.DataTypeEnum enums respectively. For some field types it is necessary to also examine other properties to determine the field type. For example, an AutoNumber field will have the same
Type property value as a normal 32-bit integer field. It is necessary to examine additional properties to determine the exact type of the field.
The table below shows how each possible Access field type appears when examined via DAO and ADO. See the footnotes for addition information on how to determine the field type when more than the Type enumeration value is required.
DAO and ADO Data Type Constants for Access Data Types
|Data Type||DAO Constant||ADO Constant|
|Base Data Types||Name||Val||Hex||Name||Val||Hex|
|Binary (Short, Fixed)||dbBinary 1||9||0x9||adVarBinary 2||204||0xCC|
|Binary (Short, Variable)||dbBinary 1||9||0x9||adVarBinary 2||204||0xCC|
|Text (Short, Fixed)||dbText 3||10||0xA||adWChar||130||0x82|
|Text (Short, Variable)||dbText 3||10||0xA||adVarWChar||202||0xCA|
|Extended Data Types|
|AutoNumber (Int32, Increment)||dbLong 4||4||0x4||adInteger 5||3||0x3|
|AutoNumber (Int32, Random)||dbLong 6||4||0x4||adInteger 7||3||0x3|
|AutoNumber (GUID)||dbGUID 8||15||0xF||adGUID 9||72||0x48|
|Hyperlink||dbMemo 10||12||0xC||adLongVarWChar 11||201||0xC9|
|Calculated||[various] 12||[not identifiable] 13|
|Complex Data Types|
|Attachment||dbAttachment||101||0x65||[not identifiable] 14|
1 DAO always returns
dbBinary. Attributes property includes the
dbVariableField (0x2) flag if VARBINARY, or the
dbFixedField (0x1) flag if fixed BINARY.
2 ADO always returns
adVarBinary. Extended property “Fixed Length” is False for VARBINARY, True for fixed BINARY.
3 Can determine if field is fixed length by examining Attributes property for
dbFixedField (0x1) vs
dbVariableField (0x2) flag.
4 Field is AutoNumber if the Attributes property includes the
dbAutoIncrField flag (0x10)
5 Field is AutoNumber if the extended property “Autoincrement” = True
6 Field is AutoNumber (random) if DefaultValue property = “GenUniqueID()”
7 Field is AutoNumber (random) if extended property “Default” = “GenUniqueID()”
8 Field is AutoNumber (GUID) if DefaultValue property = “GenGUID()”
9 Field is AutoNumber (GUID) if extended property “Default” = “GenGUID()”
10 Field is Hyperlink if Attributes includes
dbHyperlinkField (0x8000) flag
11 Field is Hyperlink if extended property “Jet OLEDB:Hyperlink” = True
12 Field is calculated if Expression property is set
13 Calculated fields cannot be detected via ADOX
14 All complex fields appear as type
adIUnknown via ADO/ADOX
Creating Fields, by Data Type
Methods of creating fields in Access
The are five distinct ways to create fields in Microsoft Access:
- Through the user interface (table designer)
- SQL submitted via DAO or the user interface (a DDL query)
- SQL submitted via ADO
- The DAO object model
- The ADOX object model
Each of the methods has limitations (see Applicable Methods, by Data Type). Thankfully for programmers all types can be created programmatically, with a small partial exception: You cannot set the size properties of multivalue (“complex” in Access terminology) fields programmatically. This means you cannot set the scale and precision of a complex decimal field via code, nor can you set the length of a complex text field via code. Three types (binary, varbinary, and fixed-length text fields) can only be created through code or SQL.
Applicable Methods, by Data Type
The following table shows which methods can be used to create each field type in Microsoft Access. Instructions on specifically how to create each type of field will be presented in a future post.
|Data Type||DDL Query||ADO DDL||ADOX||DAO||GUI|
|Base Data Types|
|Binary (Short, Fixed)||x||x||x||x|
|Binary (Short, Variable)||x||x||x|
|Text (Short, Fixed)||x||x||x|
|Text (Short, Variable)||x||x||x||x||x|
|Complex Data Types|
|AutoNumber (Int32, Increment)||x||x||x||x||x|
|AutoNumber (Int32, Random)||x||x||x||x|
|Complex Data Types|
|Multivalue Decimal||x 1||x|
|Multivalue Text||x 2||x|
1 The scale and precision properties cannot be set via DAO.
2 The length property cannot be set via DAO.