BUG: ADODB Jet 4.0 Recordset.Seek Causes Application Error on Recordset.Close

• ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2

SYMPTOMS

Closing the Recordset after the Seek may result in the following error message:

Application Error - The Instruction at '0x------' referenced memory at '0x------'. The memory could not be written.

CAUSE

ADO is not properly releasing memory after freeing the fields collection on Recordset.Close.

RESOLUTION

Recordset.Seek works if there is no mix of syntax between multiple and single-index columns. For instance, if you use the following syntax:

Recordset.Seek Array("12345"), adSeekFirstEQ 

Recordset.seek Array("12345", "12"), adSeekFirstEQ 

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

To reproduce this problem paste the following code in the General Declarations section of a new Visual Basic Standard EXE project and add a reference to the Microsoft ActiveX Data Object Library.

NOTE: This sample code is based on the NorthWind database converted to Access 2000 database format.

Option Explicit

Private Sub Command1_Click()

On Error GoTo ErrorMessage

Dim adoCn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim strCn As String

'Nwind Access97 database converted to Access2k.
strCn = App.Path & "\Northwind2k.mdb"
Set adoCn = New ADODB.Connection
With adoCn
    .Provider = "Microsoft.JET.OLEDB.4.0"
    .Open strCn, "Admin", ""
End With

Set adoRS = New ADODB.Recordset
With adoRS
    Set .ActiveConnection = adoCn
    .CursorLocation = adUseServer
    .CursorType = adOpenKeyset
    .LockType = adLockPessimistic
    .Index = "PrimaryKey"
    .Open "Order Details", adoCn, , , adCmdTableDirect
End With

'If you use the Seek String syntax before the Array you will get an Application Error.
'If you use the Array syntax first - no error.
'If you are consistent and use the String or Array syntax for both - no error.

'Uncomment either of these two lines below and you will get an Application Error.
'adoRS.Seek "10253", adSeekFirstEQ
'adoRS.Seek Array("10253"), adSeekFirstEQ
adoRS.Seek Array("10253", "49"), adSeekFirstEQ
Debug.Print adoRS.Bookmark

adoRS.Seek Array("10255", "16"), adSeekFirstEQ
'adoRS.Seek "10255", adSeekFirstEQ
Debug.Print adoRS.Bookmark

Debug.Print "Success..."
GoTo ExitSub

ErrorMessage:
    MsgBox Err.Number & " : " & vbCrLf & Err.Description

ExitSub:
    adoRs.Close
    adoCn.Close
    Set adoCn = Nothing
    Set adoRS = Nothing

End Sub 

REFERENCES

http://msdn.microsoft.com/library/officedev/off2000/acidxmethods.htm

Additional query words:

Keywords : kbADO kbDatabase kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2
Platform : WINDOWS
Issue type : kbbug