DAO -> ADO - Term replacement

[Mile-O]

What's the ADO equivalent or "improvement" on DAO's NoMatch property?

 

[dcx693]

There is no exact equivalent. You need to check the recordset's EOF property. It will be true if there was no match with the Find or Seek commands.

 

[Mile-O]

I take it that Seek has replaced FindFirst?

 

[dcx693]

Actually, I think the Seek method works the same way in both DAO and ADO (it's the fastest method, but requires an index field).

All the various Find methods in DAO (FindFirst, FindNext, etc...) have been replaced with the single Find method in ADO with various parameters. That also makes it a bit more tricky to work with.

 

[Mile-O]

I'll persevere. Thanks dcx.

As you can probably tell by my question, I've moved up in the Access world to Access 2002.

 

[Mile-O]

Keywords: ADO DAO Recordset References

Just on the off-chance that someone may search for something like this in the future:

Seven functions:

HasPriority
HasADO
HasDAO
AddADO
AddDAO
RemoveADO
RemoveDAO

all returning Boolean values.


Constants in AddDAO and AddADO may necissitate alterations.

Public Function HasPriority(ByVal strRef As String) As Boolean

    On Error GoTo Err_HasPriority
    
    Dim ref As Reference
    Dim strOther As String
    
    ' ensure that value passed is one of two types
    If strRef <> "DAO" And strRef <> "ADODB" Then Exit Function
    
    ' depending on the type passed, assign the other
    strOther = IIf(strRef = "ADODB", "DAO", "ADODB")
    
    ' look for first instance of two types in reference collection
    For Each ref In References
        ' if reference equals specified reference
        If ref.Name = strRef Then
            HasPriority = True
            Exit Function
        ' check if reference matches other type
        ElseIf ref.Name = strOther Then
            Exit Function
        End If
    Next

Exit_HasPriority:
    Set ref = Nothing
    Exit Function
    
Err_HasPriority:
    Resume Exit_HasPriority

End Function



Public Function HasADO() As Boolean

    On Error GoTo Err_HasADO
    
    Dim ref As Reference
    
    For Each ref In References
        If ref.Name = "ADODB" Then
            HasADO = True
            Exit Function
        End If
    Next
        
Exit_HasADO:
    Set ref = Nothing
    Exit Function
    
Err_HasADO:
    Resume Exit_HasADO

End Function




Public Function HasDAO() As Boolean

    On Error GoTo Err_HasDAO
    
    Dim ref As Reference
    
    For Each ref In References
        If ref.Name = "DAO" Then
            HasDAO = True
            Exit Function
        End If
    Next
    
Exit_HasDAO:
    Set ref = Nothing
    Exit Function
    
Err_HasDAO:
    Resume Exit_HasDAO

End Function




Public Function RemoveADO() As Boolean

    On Error GoTo Err_RemoveADO

    Dim ref As Reference
    Set ref = References("ADODB")
    
    References.Remove ref
    RemoveADO = True
    
Exit_RemoveADO:
    Set ref = Nothing
    Exit Function
    
Err_RemoveADO:
    Resume Exit_RemoveADO
    
End Function




Public Function RemoveDAO() As Boolean

    On Error GoTo Err_RemoveDAO

    Dim ref As Reference
    Set ref = References("DAO")
    
    References.Remove ref
    RemoveDAO = True
    
Exit_RemoveDAO:
    Set ref = Nothing
    Exit Function
    
Err_RemoveDAO:
    Resume Exit_RemoveDAO
    
End Function




Public Function AddADO() As Boolean

    On Error GoTo Err_AddADO
       
    Dim ref As Reference
    Const strADO = "C:\Program Files\Common Files\System\ADO\msado25.tlb"

    Set ref = References.AddFromFile(strADO)
    AddADO = True
    
Exit_AddADO:
    Set ref = Nothing
    Exit Function
    
Err_AddADO:
    Resume Exit_AddADO

End Function




Public Function AddDAO() As Boolean

    On Error GoTo Err_AddDAO

    Dim ref As Reference
    Const strDAO = "C:\Program Files\Common Files\Microsoft Shared\DAO\DAO350.dll"

    Set ref = References.AddFromFile(strDAO)
    AddDAO = True
    
Exit_AddDAO:
    Set ref = Nothing
    Exit Function

Err_AddDAO:
    MsgBox Err.Description
    Resume Exit_AddDAO

End Function

 

[dcx693]

Very cool functions, Mile.

As you can probably tell by my question, I've moved up in the Access world to Access 2002.

I feel ambivalent about the move to Access 2002 myself. While it's certainly more feature-rich, I find it a bit clunky. Access 97 is much faster.