Deep dive: DAO and ADO field type mapping

During the development of our Excel add-ins we were faced with numerous technical challenges. Some of our solutions are discussed in a series of articles called ‘Deep dive’.

Today we publish our first article of the series which handles the mapping of  DAO and ADO field types.

DAO and ADO

In order to implement the two-way communication between MS Excel and MS Access we had to make use of two separate application programming interfaces regarding database objects: DAO 3.6 and ADO 2.8.
Data Access object (DAO) is an API that enables you to write applications that are independent of any particular database management system (DBMS). DAO is optimized for the Microsoft Jet database engine and provides a set of data access objects (database objects, tabledef and querydef objects, recordset objects, and others). It works best with MS Access file types.
ActiveX Data Objects (ADO) is another API to data and information. In some ways it is the successor of DAO. ADO was designed to be the one data interface needed for single and multi-tier client/server and web-based data-driven solution development. Since the introduction of ADO.NET ADO is now primarily used by MS Office products like MS Excel;  Through Visual Basic for Applications (VBA) its type library enables developers to interact with other datasources. The primary benefits of ADO are its ease of use, high speed, low memory overhead, and a small disk footprint.

Object accessibility

To be able to explore the queries and tables within a MS Access from an external application like MS Excel we needed the DAO API. It is not possible to access the system tables of an Access database (particularly table MSysObjects) from outside the Access file itself. The same is true for determining the relations in the datamodel. Moreover some specific properties of data objects in MS Access can only be explored through DAO.
Examples of properties regarding Access table fields:

  • Caption
  • ColumnWidth
  • Description
  • Format
  • ValidationText

Examples regarding Access queries:

  • Parameters
  • SQL

The challenge

Because of its advantages we preferred ADO over DAO with regard to our data manipulation requirements; We only wanted to rely on ADO objects when interacting with Access. This posed a challenge since both type libraries use different field type definitions. For instance: DAO 3.6 uses the constant ‘dbSingle’ (=6) to designate the data type of a field as a single precision numeric value. ADO 2.8 on the other hand uses ‘adSingle’ (=4) for the same field. In setting up our table field objects this issue was tackled easily. We just executed the sql statement ‘SELECT * FROM [table name]’ thus retrieving the ADO fields object definitions in a ADODB.Recordset. No mapping between DAO and ADO field types needed!
For parameterized queries, however, we ran into a problem. From within Excel we wanted to allow users to execute Access queries that require parameters. To further facilitate this we also wanted to enforce the appropiate data type validations per parameter when he/she runs the query from Excel. When a query requires a numeric value as input parameter no text should be allowed as input.
However, the field type of a parameter could only be determined through DAO. The parameters collection of a DAO.QueryDef object exposes the DAO field types. Unfortunately Connexa uses an ADODB.Command object to execute (parameterized) queries: an ADO data type is required as an argument to create an ADODB.Parameter before this parameter can be appended to the command object.

The solution

In order to solve this we had to come up with a mapping routine (among other things). We relied heavily on the information on allenbrowne.com to accomplish this. Below you find the mapping routine as it ended up in the interface class (‘IDS’ stands for Interface Data Source):

'Description: Returns the ADO equivalent of a DAO data type 
'Input: iTypeDB (Integer). The DAO data type of a parameter object 
'Output: (Long). The ADO equivalent of the DAO input argument 
'Author: Emiel Nijhuis, Red Circle Software 
'Changes: Initial version, 7-7-2010 
'Reviewed: 29-7-2010; ref. RV_ENI2010_e481.docx 
'Remarks: None
Private Function IDS_MapToTypeADO(iTypeDB As Integer) As Long
    Select Case iTypeDB
        'Fixed width adWChar does not exist
        Case dbText: IDS_MapToTypeADO = adVarWChar 
        Case dbMemo: IDS_MapToTypeADO = adLongVarWChar
        Case dbByte: IDS_MapToTypeADO = adUnsignedTinyInt
        Case dbInteger: IDS_MapToTypeADO = adSmallInt
        Case dbLong: IDS_MapToTypeADO = adInteger
        Case dbSingle: IDS_MapToTypeADO = adSingle
        Case dbDouble: IDS_MapToTypeADO = adDouble
        Case dbGUID: IDS_MapToTypeADO = adGUID
        Case dbDecimal: IDS_MapToTypeADO = adNumeric
        Case dbDate: IDS_MapToTypeADO = adDate
        Case dbCurrency: IDS_MapToTypeADO = adCurrency
        Case dbBoolean: IDS_MapToTypeADO = adBoolean
        Case dbLongBinary: IDS_MapToTypeADO = adLongVarBinary
        Case dbBinary: IDS_MapToTypeADO = adVarBinary
        Case Else: IDS_MapToTypeADO = adVarWChar

    End Select
End Function

We hope it will benefit you as much as it did us.