ADO - ADO Data Types and Data Type Mapping

ADO (ActiveX Data Objects) provides a common programming interface for applications to communicate with different types of databases. When an application retrieves, inserts, or updates data, the application and the database must understand the type of each value being transferred. For example, a database may store a person's age as an integer, a person's name as text, and a registration date as a date value. ADO provides its own data type system to represent these values while communicating between the application and the underlying data source.

1. What Are ADO Data Types?

ADO data types describe the kind of value stored in an ADO object such as a Field or Parameter. ADO commonly uses the DataTypeEnum enumeration to identify these types. Examples include adInteger for integer values, adVarChar for variable-length character data, adDate for date and time values, and adBoolean for true or false values.

For example, consider a database table containing the following columns:

EmployeeID    Integer
EmployeeName  VARCHAR
Salary        Decimal
JoiningDate   Date
IsActive      Boolean

When ADO retrieves these columns through a Recordset, each field has a corresponding ADO data type. The application can examine the field's Type property to determine what kind of data it contains.

A simplified example is:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT EmployeeID, EmployeeName, Salary, JoiningDate FROM Employees", _
        connectionObject

Debug.Print rs.Fields("EmployeeID").Type
Debug.Print rs.Fields("EmployeeName").Type
Debug.Print rs.Fields("Salary").Type
Debug.Print rs.Fields("JoiningDate").Type

The Type property allows the application to identify the data type associated with each field.

2. Why Data Type Mapping Is Necessary

Different database systems do not necessarily use exactly the same data type names. SQL Server, Oracle, MySQL, Access, and other database systems can have different ways of representing similar types of information.

For example, one database might use:

VARCHAR

while another system might use a different name for variable-length character data.

ADO provides a standardized data-access layer so that an application can work with database values through ADO-defined types rather than having to depend entirely on the terminology of a particular database.

Data type mapping therefore acts as a bridge:

Database Data Type
        |
        v
ADO Data Type
        |
        v
Application Data Type

The exact conversion depends on the database provider and programming language being used.

3. Common ADO Data Types

Some commonly encountered ADO data types include the following.

ADO Type	Purpose
adInteger	Represents integer values
adSmallInt	Represents smaller integer values
adBigInt	Represents large integer values
adTinyInt	Represents very small integer values
adBoolean	Represents true or false values
adDate	Represents date and time values
adCurrency	Represents currency-related numeric values
adDecimal	Represents decimal numeric values
adNumeric	Represents numeric values with precision and scale
adSingle	Represents single-precision floating-point values
adDouble	Represents double-precision floating-point values
adChar	Represents fixed-length character data
adVarChar	Represents variable-length character data
adLongVarChar	Represents long variable-length character data
adBinary	Represents binary data
adVarBinary	Represents variable-length binary data
adLongVarBinary	Represents long binary data
adGUID	Represents globally unique identifiers

The availability and exact behavior of particular types can depend on the provider being used.

4. Mapping Database Types to ADO Types

Consider a SQL Server table:

CREATE TABLE Employees
(
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Salary DECIMAL(10,2),
    JoiningDate DATETIME,
    IsActive BIT
);

Conceptually, these types can be represented through ADO as follows:

SQL Server INT
       |
       v
ADO adInteger

SQL Server VARCHAR
       |
       v
ADO adVarChar

SQL Server DECIMAL
       |
       v
ADO adDecimal / adNumeric

SQL Server DATETIME
       |
       v
ADO adDate

SQL Server BIT
       |
       v
ADO adBoolean

This mapping allows an ADO application to work with the returned data using a consistent programming interface.

However, mapping should not be viewed as a universal one-to-one conversion table. The provider determines how database-specific types are exposed through ADO, and some database types may require special handling.

5. ADO Types and Fields

A Recordset contains one or more Field objects. Every Field represents a column or calculated value and has properties describing that value.

One important property is:

rs.Fields("EmployeeID").Type

This returns the ADO data type associated with the field.

Other useful Field properties include:

rs.Fields("EmployeeName").Name
rs.Fields("EmployeeName").Type
rs.Fields("EmployeeName").DefinedSize
rs.Fields("EmployeeName").Value

For example:

Dim fieldObject As ADODB.Field

Set fieldObject = rs.Fields("EmployeeName")

Debug.Print fieldObject.Name
Debug.Print fieldObject.Type
Debug.Print fieldObject.DefinedSize
Debug.Print fieldObject.Value

This is useful when an application needs to examine database metadata dynamically.

6. Data Type Mapping with Parameters

Data type mapping becomes especially important when parameters are used in an ADO Command.

For example, suppose an application needs to retrieve an employee using an integer ID.

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

cmd.ActiveConnection = connectionObject
cmd.CommandText = "SELECT * FROM Employees WHERE EmployeeID = ?"
cmd.CommandType = adCmdText

cmd.Parameters.Append cmd.CreateParameter( _
    "EmployeeID", adInteger, adParamInput, , 101)

Here:

Database column: EmployeeID
Database type:   INT
ADO parameter:   adInteger
Parameter value: 101

The parameter's ADO type tells the provider what kind of value is being supplied.

Correct parameter typing can make database operations more predictable and can help avoid unnecessary type conversions.

7. Precision and Scale

Numeric values require special attention because simply identifying a value as numeric may not be sufficient.

Consider:

Salary DECIMAL(10,2)

Here:

Precision = 10
Scale     = 2

Precision represents the total number of digits, while scale represents the number of digits after the decimal point.

A corresponding ADO parameter can be configured with appropriate precision and scale:

Dim p As ADODB.Parameter

Set p = cmd.CreateParameter( _
    "Salary", adDecimal, adParamInput)

p.Precision = 10
p.NumericScale = 2
p.Value = 45000.75

cmd.Parameters.Append p

This is important when working with financial, accounting, measurement, or other precision-sensitive data.

8. Character Data and String Mapping

Character fields commonly require mapping between database character types and ADO character types.

For example:

VARCHAR(100)

may be represented using:

adVarChar

A parameter can be created as:

cmd.Parameters.Append cmd.CreateParameter( _
    "EmployeeName", adVarChar, adParamInput, 100, "Rahul")

The size parameter is significant because it describes the expected length of the character value.

For Unicode data, applications may use appropriate Unicode-capable ADO types, depending on the provider and database system.

Correct character-type selection is particularly important when an application needs to handle multilingual text.

9. Date and Time Mapping

Dates and times are another important area of data type mapping.

A database may provide a date/time column, while the programming environment represents the value using its own date representation.

ADO provides:

adDate

for date and time values.

For example:

Dim joiningDate As Variant

joiningDate = rs.Fields("JoiningDate").Value

Debug.Print joiningDate

Applications should avoid treating date values as ordinary strings whenever possible. Keeping values as date/time types allows the application to perform date calculations and comparisons more reliably.

10. Binary Data Mapping

Databases can also store binary information such as images, documents, or other binary objects.

ADO provides binary-oriented types such as:

adBinary
adVarBinary
adLongVarBinary

For example, a database column storing a large binary object may be exposed through a long binary ADO type.

Applications must handle such values differently from ordinary text because binary data should not be interpreted as character data without an appropriate conversion.

11. Data Type Conversion

Sometimes the database type and the application's desired type are different. In such cases, conversion may occur.

For example:

Database
INT
 |
 v
ADO
adInteger
 |
 v
Application
Integer

Another example could involve a database numeric value being converted into a programming-language numeric type.

Conversions can occur:

Database -> Provider -> ADO -> Programming Language

or in the opposite direction:

Programming Language -> ADO Parameter -> Provider -> Database

Unnecessary or incompatible conversions can cause errors or loss of precision, so applications should use appropriate data types whenever possible.

12. Type Mismatch Problems

Incorrect type mapping can produce errors.

For example, suppose a database expects:

EmployeeID = Integer

but the application supplies a value that cannot be converted into an integer.

Potential problems include:

Type mismatch
Conversion failure
Numeric overflow
Loss of precision
Invalid date conversion
String truncation
Provider-specific errors

For this reason, parameter types should normally correspond appropriately to the underlying database columns.

13. Importance of Provider-Specific Behavior

ADO provides a common interface, but the underlying OLE DB provider still plays an important role.

The general architecture can be viewed as:

Application
    |
    v
ADO
    |
    v
OLE DB Provider
    |
    v
Database

The provider is responsible for communicating with the particular data source. Consequently, some data type mappings and conversions can vary between providers.

This means developers should not assume that every database type will behave identically across all data sources.

14. Practical Example

Suppose a database contains:

CustomerID     INT
CustomerName   VARCHAR(100)
Balance        DECIMAL(12,2)
BirthDate      DATETIME
Active         BIT

An ADO application might conceptually work with the data like this:

CustomerID
Database: INT
ADO:      adInteger

CustomerName
Database: VARCHAR(100)
ADO:      adVarChar

Balance
Database: DECIMAL(12,2)
ADO:      adDecimal/adNumeric

BirthDate
Database: DATETIME
ADO:      adDate

Active
Database: BIT
ADO:      adBoolean

The application can then retrieve these values through the Recordset:

Debug.Print rs.Fields("CustomerID").Value
Debug.Print rs.Fields("CustomerName").Value
Debug.Print rs.Fields("Balance").Value
Debug.Print rs.Fields("BirthDate").Value
Debug.Print rs.Fields("Active").Value

The application does not have to manually interpret the underlying database representation for every field.

15. Advantages of Proper Data Type Mapping

Proper data type mapping provides several benefits.

First, it reduces type conversion errors because the application and database are working with compatible representations.

Second, it helps preserve numeric precision. This is particularly important for decimal and currency values.

Third, it improves the reliability of parameterized database operations.

Fourth, it allows applications to inspect field metadata and work dynamically with database structures.

Fifth, it makes applications more portable because ADO provides a standardized data-access model across supported providers.

Finally, appropriate data types can improve the efficiency and predictability of database communication.

Conclusion

ADO data types provide a standardized way for applications to represent and exchange values with databases. Data type mapping connects database-specific types with ADO's DataTypeEnum values and ultimately with the application's own data types. Types such as adInteger, adVarChar, adDecimal, adDate, adBoolean, and binary types allow ADO applications to work with different kinds of database information.

Understanding data type mapping is particularly important when working with Fields, Parameters, Commands, numeric precision, dates, Unicode text, and binary data. Developers should also remember that the exact mapping can depend on the underlying database provider. Correctly choosing and handling ADO data types helps prevent conversion errors, preserve data accuracy, and make database applications more reliable.