VBA - Using Typeof ... Is ADODB.Recordset Results in Compile Error

Question

I am building a function with a set of supporting sub-functions to create ADOX.Catalog objects to help me build automation for Access database generation.

I like to use late-binding for my applications because my user base doesn't always have the same version of office applications, so I can't always rely on them having the same versions of the libraries I'm calling.

My public function accepts several objects as parameters, but I need to make sure they're actually ADODB.Recordset objects before I start processing them. I referred to the msdn article at https://msdn.microsoft.com/en-us/library/s4zz68xc.aspx to get started, and I'm trying to use If TypeOf ... Is ADODB.Recordset per the article's recommendation, but it generates the following error:

Compile error:
User-defined type not defined

Here is a snippet of my code. The first offending line is TypeOf adoRsColumns Is ADODB.Recordset:

Public Function ADOX_Table_Factory( _
ByVal strTblName As String, _
Optional ByVal adoRsColumns As Object, _
Optional ByVal adoRsIndexes As Object, _
Optional ByVal adoRsKeys As Object _
) As Object

'Init objects/variables.
Set ADOX_Table_Factory = CreateObject("ADOX.Table")

'Begin interactions with the new table object.
With ADOX_Table_Factory
    .Name = strTblName

    'Check if we've received an ADO recordset for the column(s).
    If TypeOf adoRsColumns Is ADODB.Recordset Then
        'Check that the recordset contains rows.
        If Not (adoRsColumns.BOF And adoRsColumns.EOF) Then
            'Loop through the column definitions.
            Do
                .Columns.Append ADOX_Column_Factory(adoRsColumns.Fields(0), adoRsColumns.Fields(1), adoRsColumns.Fields(2), adoRsColumns.Fields(3))
            Loop Until adoRsColumns.EOF
        End If
    End If

My Googling has not yielded any results that have helped me get around this error. I have confirmed this code works if I set a reference to the ADO library. I have also confirmed, via the TypeName function, that the objects are identified by name as Recordset. If I replace TypeOf adoRsColumns Is ADODB.Recordset with TypeOf adoRsColumns Is Recordset, however, then the test evaluates false and the desired code doesn't execute. I haven't resorted to a string comparison to TypeName's output because, as stated in the MSDN article, TypeOf ... Is is faster.

Thanks in advance for any assistance!

Answer 1

Just to recap, without an ADO reference included in your project, you get a compile error at this line:

If TypeOf adoRsColumns Is ADODB.Recordset Then

Without the reference, VBA doesn't recognize ADODB.Recordset The situation is basically the same as if you tried to declare Dim rs As ADODB.Recordset without the reference. That declaration would trigger the same compile error.

There is no way to use ADODB.Recordset as a recognized type without the reference.

As an alternative approach, you could create a custom function to check whether the object supports a method or property which is available in an ADODB.Recordset but not in a DAO.Recordset

This one checks whether the recordset includes a Supports method. That method is available in an ADODB but not DAO Recordset.

Public Function IsAdoRecordset(ByRef pObject As Object) As Boolean
    Const adAddNew As Long = 16778240
    Dim lngTmp As Long
    Dim blnReturn As Boolean
    Dim strMsg As String

On Error GoTo ErrorHandler

    blnReturn = False
    If TypeName(pObject) = "Recordset" Then
        lngTmp = pObject.Supports(adAddNew)
        blnReturn = True
    End If

ExitHere:
    On Error GoTo 0
    IsAdoRecordset = blnReturn
    Exit Function

ErrorHandler:
    Select Case Err.Number
    Case 438  ' Object doesn't support this property or method
        ' leave blnReturn = False
    Case Else
        ' notify user about any other error
        strMsg = "Error " & Err.Number & " (" & Err.Description _
            & ") in procedure IsAdoRecordset"
        MsgBox strMsg
    End Select
    Resume ExitHere

End Function