Microsoft Access Field Data Type Reference

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.

Contents

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!

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
Binary (Long) dbLongBinary 11 0xB   adLongVarBinary 205 0xCD
Boolean dbBoolean 1 0x1   adBoolean 11 0xB
Byte dbByte 2 0x2   adTinyInt 16 0x10
Currency dbCurrency 5 0x5   adCurrency 6 0x6
DateTime dbDate 8 0x8   adDate 7 0x7
GUID dbGUID 15 0xF   adGUID 72 0x48
Decimal dbDecimal 20 0x14   adNumeric 131 0x83
Single dbSingle 6 0x6   adSingle 4 0x4
Double dbDouble 7 0x7   adDouble 5 0x5
Int16 dbInteger 3 0x3   adSmallInt 2 0x2
Int32 dbLong 4 0x4   adInteger 3 0x3
Text (Short, Fixed) dbText 3 10 0xA   adWChar 130 0x82
Text (Short, Variable) dbText 3 10 0xA   adVarWChar 202 0xCA
Text (Long) dbMemo 12 0xC   adLongVarWChar 203 0xCB
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    
Multivalue Byte dbComplexByte 102 0x66        
Multivalue Decimal dbComplexDecimal 108 0x6C        
Multivalue Double dbComplexDouble 106 0x6A        
Multivalue GUID dbComplexGUID 107 0x6B        
Multivalue Int16 dbComplexInteger 103 0x67        
Multivalue Int32 dbComplexLong 104 0x68        
Multivalue Single dbComplexSingle 105 0x69        
Multivalue Text dbComplexText 109 0x6D        

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:

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    
Binary (Long) x x x x x
Boolean x x x x x
Byte x x x x x
Currency x x x x x
DateTime x x x x x
GUID x x x x x
Decimal   x x   x
Single x x x x x
Double x x x x x
Int16 x x x x x
Int32 x x x x x
Text (Short, Fixed) x x x    
Text (Short, Variable) x x x x x
Text (Long) x x x x x
Complex Data Types          
AutoNumber (Int32, Increment) x x x x x
AutoNumber (Int32, Random)   x x x x
AutoNumber (GUID)     x   x
Hyperlink     x x x
Calculated       x x
Complex Data Types          
Attachment       x x
Multivalue Byte       x x
Multivalue Decimal       x 1 x
Multivalue Double       x x
Multivalue GUID       x x
Multivalue Int16       x x
Multivalue Int32       x x
Multivalue Single       x 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.