Function to Get Data Type DAO

Data type is the most important property in a field. Data type determines what type of data should be stored in a field of a table and how to present the data in a report. For example, a field with Text data type stores data in a text or numerical character. For a field that has Numeric data type, this field can only stores numerical data.

MS Access provides ten different data types:

1. Text: for short, alphanumeric values, such as a last name or a street address.
2. Memo: for long blocks of text and text that use text formatting. A typical use of a Memo field would be a detailed product description.
3. Number: for numeric values, such as distances. Note that there is a separate data type for currency.
   
   1. Byte: for number between 0 and 255
   2. Integer: for number -32,768 and -32,768.
   3. Long : for long integer number between -2,147,483,648 to 2,147,483,647
   4. Single: for number between -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
   5. Double: for number between -1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
   6. Replication ID, for storing a globally unique identifier (GUID) required for replication
   7. Decimal: for number between -9.999E27 and 9.999E27
4. Date/Time: for date and time.
5. Currency: for monetary values.
6. AutoNumber: numbers that are automatically generated for each record.
7. Yes/No: for Boolean values.
8. OLE Object: for Object Link and Embedded, such as Word documents.
9. Hyperlink: hyperlinks, such as URL address and e-mail addresses.
10. Attachment Files: such as digital photos. Multiple files can be attached per record. This data type is not available in earlier versions of Access.

Based on the above types, we can create VBA code to get the related data type. The following function can be performed to get the data type:

Function getFieldTypeArray(intFieldType As Integer) As Variant
  Dim strFieldType As String
  Dim varFieldType As Variant
On Error GoTo Err_Msg
  Select Case intFieldType
'Numeric
    Case dbBigInt: varFieldType = Array("Numeric", "Big Integer", dbBigInt)
    Case dbByte: varFieldType = Array("Numeric", "Byte", dbByte)
    Case dbCurrency: varFieldType = Array("Numeric", "Currency", dbCurrency)
    Case dbNumeric: varFieldType = Array("Numeric", "Numeric", dbNumeric)
    Case dbDecimal: varFieldType = Array("Numeric", "Decimal", dbDecimal)
    Case dbSingle: varFieldType = Array("Numeric", "Single", dbSingle)
    Case dbDouble: varFieldType = Array("Numeric", "Double", dbDouble)
    Case dbInteger: varFieldType = Array("Numeric", "Integer", dbInteger)
    Case dbLong: varFieldType = Array("Numeric", "Long Integer", dbLong)
'Text
    Case dbChar: varFieldType = Array("Text", "Char", dbChar)
    Case dbMemo: varFieldType = Array("Text", "Memo", dbMemo)
    Case dbText: varFieldType = Array("Text", "Text", dbText)
'Date and Time
    Case dbDate: varFieldType = Array("Date", "Date", dbDate)
    Case dbTime: varFieldType = Array("Date", "Time", dbTime)
    Case dbTimeStamp: varFieldType = Array("Date", "Time Stamp", dbTimeStamp)
'Binary
    Case dbBinary: varFieldType = Array("Numeric", "Binary", dbBinary)
    Case dbLongBinary: varFieldType = Array("Binary", "OLE Object (Long Binary)", dbLongBinary)
    Case dbVarBinary: varFieldType = Array("Binary", "VarBinary", dbVarBinary)
'Others
    Case dbBoolean: varFieldType = Array("Boolean", "Yes/No", dbBoolean)
    Case dbAttachment: varFieldType = Array("Attachment", "Attachment", dbAttachment)
    Case dbFloat: varFieldType = Array("Float", "Float", dbFloat)
    Case dbGUID: varFieldType = Array("GUID", "Replication Id (GUID)", dbGUID)
    Case Else: varFieldType = Array("Not Specified", "Not Specified", "Not Specified")
  End Select
  getFieldTypeArray = varFieldType
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function getFieldTypeArray, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

The output for getFieldTypeArray function is array variant consists of three index items:

1. Index item 0, describes the general group of type.
2. Index item 1, mentions the field data type as can be seen in MS Access table design view.
3. Index item 2, states the constant related with the data type, using DAO.

Implementation

To implement the above function, we must:

1. Open a database using letDbsOpen
2. Open a recordset/table using letRsOpen
3. Iterate the fields in the recordset
4. Get the field type resulted from iteration process
5. Implement the getFieldTypeArray as follows: getFieldTypeArray ([field type number]) ([index item number])

In a simple way, we can use the following VBA code to get the list of data types:

Function getFieldTypeArrayExample()
  Dim i As Integer, iMax As Integer
  iMax = 11
  For i = 1 To iMax
    If getFieldTypeArray(i)(0) <> "not specified" Then
      Debug.Print "Group: " & getFieldTypeArray(i)(0) & _
                  ", Data Type Name: " & getFieldTypeArray(i)(1) & _
                  ", Data Type Const: " & getFieldTypeArray(i)(2)
    End If
  Next i
End Function

Running the getFieldTypeArrayExample function, the output are as follows:

Group: Boolean, Data Type Name: Yes/No, Data Type Const: 1
Group: Numeric, Data Type Name: Byte, Data Type Const: 2
Group: Numeric, Data Type Name: Integer, Data Type Const: 3
Group: Numeric, Data Type Name: Long Integer, Data Type Const: 4
Group: Numeric, Data Type Name: Currency, Data Type Const: 5
Group: Numeric, Data Type Name: Single, Data Type Const: 6
Group: Numeric, Data Type Name: Double, Data Type Const: 7
Group: Date, Data Type Name: Date, Data Type Const: 8
Group: Numeric, Data Type Name: Binary, Data Type Const: 9
Group: Text, Data Type Name: Text, Data Type Const: 10
Group: Binary, Data Type Name: OLE Object (Long Binary), Data Type Const: 11