During a recent project, I encountered an intriguing quirk in Microsoft Access that’s worth sharing. This peculiar behavior occurs when mixing ADODB and DAO recordsets in form events, leading to unexpected results that could easily be mistaken for database corruption.
 

The Setup

I initially created a form with a dynamically built ADODB recordset in the Form_Open event:

Private Sub Form_Open(Cancel As Integer)
    Dim rstVirtual            As Object

    Set rstVirtual = CreateObject("ADODB.Recordset")
    With rstVirtual
        .Fields.Append "ItemId", 20
        .Fields.Append "ItemDescription", 200, 50

        .CursorType = 3    ' adOpenStatic
        .CursorLocation = 3    ' adUseClient
        .LockType = 3    ' adLockOptimistic
        .Open
    End With

    '...
    
    Set Me.Recordset = rstVirtual
End Sub

The Unexpected Twist

In the Form_Close event, I intended to process the form’s data. However, I inadvertently used a DAO recordset instead of an ADODB recordset:

Private Sub Form_Close()
    Dim rs                    As DAO.Recordset
    
    Set rs = Me.RecordsetClone
    '...
End Sub

The Surprising Result

Now, if you didn’t notice, I made a big mistake! That’s right, I initially created an ADODB recordset and assigned it to the form, but in my close event I defined the variable as a DAO recordset and then try to assign the form’s recordset (ADODB) to the DAO variable.

Instead of the expected “Type mismatch” error, Access displayed a Data Source dialog :

I don’t know what to say, but this is just odd! Initially, when this was happening, I actually thought my db or maybe even Access itself had become corrupt. Stepping through the code allowed me to determine the issue lied with the incompatibility of variables types.

Making things even weirder is the fact that only .RecordsetClone triggers this behavior and using .Recordset instead generates the “Type mismatch” error I was expecting to see originally.

Does this fall into the category of a bug, IMHO yes as I’d expect to get an error message just like the one received when using .Recordset, certainly not a Data Source dialog!

So, if like me, one day you start getting a Data Source dialog popping up out of nowhere when executing your code, look at your variables carefully!