Microsoft Access Field Data Type Reference

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.

Contents

• List of Data Types
• Examining Data Types
• Creating Fields, by Data Type

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 nchar and 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 char or varchar indicate ANSI text. Both the char and 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!

¹ 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

¹ DAO always returns dbBinary. Attributes property includes the dbVariableField (0x2) flag if VARBINARY, or the dbFixedField (0x1) flag if fixed BINARY.
² ADO always returns adVarBinary. Extended property “Fixed Length” is False for VARBINARY, True for fixed BINARY.
³ Can determine if field is fixed length by examining Attributes property for dbFixedField (0x1) vs dbVariableField (0x2) flag.
⁴ Field is AutoNumber if the Attributes property includes the dbAutoIncrField flag (0x10)
⁵ Field is AutoNumber if the extended property “Autoincrement” = True
⁶ Field is AutoNumber (random) if DefaultValue property = “GenUniqueID()”
⁷ Field is AutoNumber (random) if extended property “Default” = “GenUniqueID()”
⁸ Field is AutoNumber (GUID) if DefaultValue property = “GenGUID()”
⁹ Field is AutoNumber (GUID) if extended property “Default” = “GenGUID()”
¹⁰ Field is Hyperlink if Attributes includes dbHyperlinkField (0x8000) flag
¹¹ Field is Hyperlink if extended property “Jet OLEDB:Hyperlink” = True
¹² Field is calculated if Expression property is set
¹³ Calculated fields cannot be detected via ADOX
¹⁴ 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.

¹ The scale and precision properties cannot be set via DAO.
² The length property cannot be set via DAO.