I apologize if this is not the right forum for VB6 so please send me to the right place if necessary.
I am using an ADO data control to access an MS Access 2000 database. I must use a server-side cursor to use pessimistic locking due to multi-users. I am opening an ADO recordset and assigning it to the ADO data control's recordset property in order to reuse the connection. The ADO data controls's recordset filter property is being set to limit the records returned to a specific account. All works fine until the Access .ldb file is not closed. I am closing everything I can think of but when the form is unloaded, the .ldb file persists. I can set the recordset.filter property the first time and still exit and the .ldb file is released. But if I loop around and reset the filter property to a different account, the .ldb is no longer released.

In form_activate event:
Set cn = New adodb.Connection
With cn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Properties("Data Source") = DPATH$ + DBName$
.Properties("Jet OLEDBatabase Locking Mode") = 1 'row-level locking
Screen.MousePointer = vbHourglass
.Open
End With
Set SOrs = New adodb.Recordset
mySQL = "Select * from ORDERS order by Acctnum, ODATE"
SOrs.Open mySQL, cn, adOpenDynamic, adLockPessimistic, adCmdText '
Set AdodcSO.Recordset = SOrs
adodcso.refresh

...
A different ADO data control is used to navigate through the customer accounts. There is a bound label control on the form to react to a change in the account#.
In the label's _Change event:
AdodcSO.Recordset.Filter = ("AcctNum = '" & lblAcctnum(1).Caption & "'")

In the form_unload event:
adodcSO.recordset.close
sors.close
cn.close
set adodcso.recordset = nothing
Set AdodcSO.Recordset.ActiveConnection = Nothing 'getting desperate here
set SOrs = nothing
set cn = nothing

...as mentioned....setting the filter property the first time works fine. If I don't navigate to a different customer but just arrive on the first customer which has orders displayed per the filter, I can even scroll through the orders for that customer in the adodcSO control. Then exit and the .ldb file is released. However if I scroll through the customer data control and it fires off a new filter, upon exiting the .ldb file remains and the database cannot be used exclusively for other purposes because it looks like it is still locked. If I comment out the filter assignment, I can still scroll between accounts (even though the orders then don't match up with the right account), and upon exiting, the .ldb file is released with no problem. This is all testing where I am the only user, so no chance of other users causing the .ldb lock.
I pretty much don't have any hair left. any help out there?

Until the connection is closed, the .ldb file will stay there. The recordset has nothing to do with it.

Personally, I don't use data controls, for a whole series of reasons. But, keep in mind that you can open the connection in a batch mode, and after the recordset is opened, you can set the recordset's activeconnection to nothing. This results in a disconnected recordset. The data is there, but the database connection is not. At this point, you can close the connection, and release it from memory. The recordset and its data live on in your app.

Later on, you can if you wish, re-open the connection, and reconnect the recordset to the connection again, And, if you have been making edits to the recordset, you can then apply the changes to the underlying table.

This approach of disconnected recordsets is the preferred method for accessing an access database when multiple users are connecting to the datafile. When performing batch updates, however, you will need to address conflicting records (records may have been updated added, or deleted by other users while your recordset is disconnected.) How your code would handle conflicting records in a batch update can be as simple as reporting the conflict and restoring your recordset to it's original state to maintaining a log of the records which had conflicts, to indivisually looking at each record which is in conflict and handling each record individually. The conflict resolution can take place before the recordset is batch updated, and/or after a recordset is batch updated.

The book, Serious ADO:Universal Data Access with Visual Basic, written by Rob MacDonald (apress) is an invaluable resource - I highly recommend it for anyone who uses ADO. MacDonald devotes an entire chapter to disconnected recordsets, for instance.

Thank you for the reply. I'm not sure why the statement:
cn.close
does not close my connection and release the .ldb file then.
After lots of research I understand the reasons for staying away from ADODC.

However, what I did to get around this and works, is to remove the filter:
AdodcSO.Recordset.Filter = ("AcctNum = '" & lblAcctnum(1).Caption & "'")

So instead of that, I modify the text of the source to
mySQL = "Select * from ORDERS Where Acctnum = '" & lblAcctNum(1) & " ' "
AdodcSO.RecordSource = mySQL
AdodcSO.Refresh

...Which actually works and doesn't leave the .ldb file in use. I had read not to use the ADODC.refresh as that creates another connection, but it solved my problem in this case.
Thanks again.

You could also use

[code]AdodcSO.Recordset.Filter = adFilterNone[/quote]

to remove the filter.