VBA wrapper for ADODB objects

Currently, for all Excel/VBA database-related working, I have been using Microsoft ADO (ActiveX Data Object library). It's already a bit old technology already and its enhanced brother ADO.NET is very unfortunately available only for .NET. If ADO is completely new topic for you, there is a lot of information available in Google. You should get yourself familiar with the following ADO objects and their corresponding important methods and properties to get started:

Connection (open, close, state)
Recordset (open, close, state, fields)

With these methods, properties and some good working example, you should have enough information to understand and learn to work with ADO connection and recordset.

In this post, I will open up my VBA wrapper for Microsoft ADO. There is a couple of things you should be aware. ADO is technically speaking dll library, what should be referenced before you can use it. Now, you have two options:

Early binding

You can reference it in a "standard way": (VB editor -- Tools -- Option -- Microsoft ActiveX Data Objects 6.X Library). This is called early binding. Now, with this approach, you have to do this referencing every time you import your custom VBA wrapper class into any new VBA project. The upside is, that all ADO object methods and properties are available in your intellisense. When you use early binding, you create your connection object like this:

Dim cn As New ADODB.Connection

Late binding

In this case, you do not need to do any dll referencing in your VB editor. You are doing it implicitly in your program. The only (AFAIK) notably downside is, that all different object methods and properties are not available in your intellisense anymore. When you use late binding, you create your connection object like this:

Dim cn As Object: Set cn = CreateObject(“ADODB.Connection”)

VBA ADO Wrapper interface

Example wrapper has only one public interface function "cGetRecordset", which returns an object for its caller (ADODB recordset) and takes in four arguments.

cUserID - your personal database username (can be empty string)
cPassword - your personal database password  (can be empty string)
cSqlQueryString - working SQL string
cDSN - connection string

I think that all the other arguments, except connection string should be self-explainable. In a connection string, we define the protocol, what we are using for getting an access to a database. Different database types are requiring different protocols for accessing them. Moreover, in a connection string we define data source from where the data is going to be retrieved. For working examples, the following website is an excellent reference for different connection strings available for numerous different databases: http://connectionstrings.com/

Below is my example tester program and wrapper class for retrieving data from MS Access database. Note, that I am using late-binding, in order to avoid any dll referencing when I want to use the wrapper in my new VBA projects. Note also, that the wrapper returns an object, having a type of ADODB recordset. You have to know how to "disassemble" data from ADO recordset back to program-level. Example is given in a tester program (NxM matrix).

Now, that's all I wanted to share about Microsoft ADO this time. Personally, I find it to be flexible tool for all database-related work in Excel/VBA. Have a nice day!
-Mike

' TESTER PROGRAM (STANDARD VBA MODULE)
'
Option Explicit
'
Sub tester()
    '
    On Error GoTo errorHandler
    '
    Dim data As New ADO_wrapper
    Dim sqlQueryString As String: sqlQueryString = "SELECT * FROM bonds"
    Dim connectionString As String: connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mka\temp\bondPosition.accdb;"
    '
    Dim result As Object
    Set result = data.cGetRecordset(connectionString, sqlQueryString)
    '
    ' Do your things with data here
    Dim i As Long, j As Long
    For i = 1 To result.RecordCount
        For j = 1 To result.Fields.Count
            Debug.Print result.Fields(j - 1)
        Next j
        result.movenext
    Next i
    '
    Set result = Nothing
    Set data = Nothing
    Exit Sub
errorHandler:
    Debug.Print Err.Source & ", " & Err.Description
End Sub
'

' ADO WRAPPER (VBA CLASS MODULE)
'
Option Explicit
'
Private Const CONST_AD_USE_CLIENT = 3
Private Const CONST_AD_OPEN_STATIC = 3
Private Const CONST_AD_LOCK_READONLY = 1
'
Private cUserID As String
Private cPassword As String
Private cDSN As String
Private cSqlQueryString As String
'
Private cConnection As Object 'ADODB.Connection
Private cRecordset As Object 'ADODB.Recordset
'
Public Function cGetRecordset(ByVal DSN As String, ByVal sqlQueryString As String, _
Optional ByVal userID As String, Optional ByVal password As String) As Object 'ADODB.Recordset
    '
    cUserID = userID
    cPassword = password
    cDSN = DSN
    cSqlQueryString = sqlQueryString
    '
    cOpenConnection
    cOpenRecordset
    '
    Set cGetRecordset = cRecordset
End Function
'
Private Function cOpenConnection()
    '
    Set cConnection = CreateObject("ADODB.Connection") 'New ADODB.Connection
    cConnection.Open cDSN, cUserID, cPassword
End Function
'
Private Function cCloseConnection()
    '
    cConnection.Close
    Set cConnection = Nothing
End Function
'
Private Function cOpenRecordset()
    '
    Set cRecordset = CreateObject("ADODB.Recordset") 'New ADODB.Recordset
    cRecordset.CursorLocation = CONST_AD_USE_CLIENT
    cRecordset.Open cSqlQueryString, cConnection, CONST_AD_OPEN_STATIC, CONST_AD_LOCK_READONLY
End Function
'
Private Function cCloseRecordset()
    '
    cRecordset.Close
    Set cRecordset = Nothing
End Function
'
Private Sub Class_Terminate()
    '
    If Not (cRecordset Is Nothing) Then Set cRecordset = Nothing
    If Not (cConnection Is Nothing) Then Set cConnection = Nothing
End Sub
'