Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: VB6 ADO data control and server-side cursor with filter and .ldb file

    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?

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Last edited by loquin; 11-16-11 at 16:20.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Nov 2011
    Posts
    2
    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.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You could also use

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

    to remove the filter.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •