ADO - Type property

In ADO (ActiveX Data Objects), the Type property is used to identify the data type of a Field, Parameter, or Property object.

It tells you what kind of data (e.g., integer, string, date, etc.) the object holds — using ADO data type constants (like adInteger, adVarChar, adDate, etc.).

 1. Field.Type

When you’re working with a Recordset, each column (field) has a Type property that indicates the data type of the column.

Example:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM Employees", conn

Dim fld As ADODB.Field
For Each fld In rs.Fields
    Debug.Print fld.Name & " - " & fld.Type
Next

This will print something like:

EmployeeID - 3
FirstName  - 200
HireDate   - 7

(where the numbers correspond to ADO data type constants).

To make it clearer, you can compare fld.Type to the constants defined in the ADO library (e.g., adInteger = 3, adVarChar = 200, adDate = 7).

 2. Parameter.Type

When using Command objects (for stored procedures or parameterized SQL), each Parameter also has a Type property that specifies what kind of data the parameter holds.

Example:

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "sp_AddEmployee"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("EmpName", adVarChar, adParamInput, 50)
cmd.Parameters.Append prm

Debug.Print prm.Type   ' Displays 200 (adVarChar)

3. Property.Type

Some ADO objects (like Connection or Recordset) have dynamic properties that also have a Type property describing the kind of value they store.

Example:

Dim prop As ADODB.Property
For Each prop In conn.Properties
    Debug.Print prop.Name & " - " & prop.Type
Next

 Common ADO Data Type Constants

Constant Name Value Description
adInteger 3 4-byte integer
adSmallInt 2 2-byte integer
adTinyInt 16 1-byte integer
adBigInt 20 8-byte integer
adSingle 4 Single-precision float
adDouble 5 Double-precision float
adCurrency 6 Currency value
adDate 7 Date/time
adBoolean 11 True/False
adVarChar 200 Variable-length string
adChar 129 Fixed-length string
adLongVarChar 201 Long text (e.g., Memo)
adVarWChar 202 Unicode variable-length string
adDecimal 14 Decimal number
adGUID 72 GUID value

 Summary Table

Object Purpose of Type Example
Field.Type Shows the data type of a field in a Recordset rs.Fields("Salary").Type
Parameter.Type Defines or returns the data type of a parameter cmd.Parameters("EmpID").Type = adInteger
Property.Type Indicates the data type of an ADO property conn.Properties("Connect Timeout").Type

Key Points:

  • The Type property returns an integer constant representing an ADO data type.

  • You can read it to identify the data type, or set it (for Parameter objects) before executing a command.

  • It’s especially useful for debugging, validation, or dynamic code that adapts to different database schemas.