none
ADO Cursor types and Recordset and what it does - New user here. Please advice

    Question

  • Hi everyone! 'm new to access and really need help here. I have read few articles; including the one i found here called "Understanding ADO default cursor types"

    'm trying to use some codes like the one i have below. What I really do want to understand is .....

    What kind of recordset, cursor type and also the connection string/type should i use when adding/editing/deleting data to my access table? so that it does not prevent other users from doing same activity in same table maybe sometimes in the same time...[1 or more users could be accessing the database from frontend]

    And what type do I use when I just need to find out if user id is there in the table etc or retriving data etc.. 

    Thank you all in advance for helping me out again like always.

     

    [code]

    Dim strCNXN As String
    Dim CNXN As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim StrSql As String

        Set CNXN = New ADODB.Connection
        strCNXN = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=E:\Access\DAILY EXCERCISE_.......
        CNXN.Open strCNXN
        Set rst = New ADODB.Recordset
       
        rst.CursorLocation = adUseClient
        rst.CursorType = adOpenStatic
        rst.LockType = adLockBatchOptimistic

        rst.Open "MyTab1", CNXN, , adLockPessimistic    
        rst.AddNew
         rst!X11 = "x11"
         rst!X12 = "x12"
        rst.Update


           [/code]


    Pedie
    Tuesday, September 06, 2011 8:50 PM

Answers

  • Check http://msdn.microsoft.com/en-us/library/aa141422(v=office.10).aspx.

     

    Note that ADO can downgrade, "gracefully" (no warning, no error) a recordset type (see the article, close to the end). As example, if you ask for an ADO Dynaset against Jet, which is not supported, it will be downgraded to an ADO Keyset (the closest match to the DAO Dynaset), as you can check by looking at the recordset type once you have opened the recordset. Generally, if you need update (and data on the server side, the default for Jet), you will use a Keyset. You use a FireHose, or ReadOnlyForwardOnly, if you want speed and light overhead, but it cannot move backward (so it is great to scan "once", as example).

     

    If you use Access' Forms, try DAO instead of ADO.

     

    If you need disconnected recordsets, try ADONet instead of ADO.




    Tuesday, September 06, 2011 10:57 PM
  • If you use ServerSide, and wish to update the data, then the static is promoted to a keyset:

    adOpenStatic adLockOptimistic
    adLockPessimistic
    adBatchOptimistic
    For all other lock types, a Keyset cursor is returned.

     

      And then, the relevant locking mechanic is, for a keyset, occurs as explained in the reference:

     

    adOpenKeyset adLockOptimistic The Recordset object has an updatable, scrollable cursor. You can see updates and deletions, but not insertions made by other users. The record is not locked to save updates to the current record until the Update method is called, or when you move to a different record.
    adOpenKeyset adLockPessimistic The Recordset object has an updatable, scrollable cursor. You can see updates and deletions, but not insertions made by other users. The record is locked when the first field is modified.

     

    "

    • Marked as answer by Pedie Nz Thursday, September 08, 2011 7:52 PM
    Wednesday, September 07, 2011 10:10 PM

All replies

  • Check http://msdn.microsoft.com/en-us/library/aa141422(v=office.10).aspx.

     

    Note that ADO can downgrade, "gracefully" (no warning, no error) a recordset type (see the article, close to the end). As example, if you ask for an ADO Dynaset against Jet, which is not supported, it will be downgraded to an ADO Keyset (the closest match to the DAO Dynaset), as you can check by looking at the recordset type once you have opened the recordset. Generally, if you need update (and data on the server side, the default for Jet), you will use a Keyset. You use a FireHose, or ReadOnlyForwardOnly, if you want speed and light overhead, but it cannot move backward (so it is great to scan "once", as example).

     

    If you use Access' Forms, try DAO instead of ADO.

     

    If you need disconnected recordsets, try ADONet instead of ADO.




    Tuesday, September 06, 2011 10:57 PM
  • Hi Vanderghast, i have gone through the link you referred...thank you very much.

    So do i go for connection like this with cursor

    strCNXN = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=......

    rst.CursorType = adOpenStatic

    rst.Open StrSql, CNXN, , adLockOptimistic 'adLockPessimistic

    My concern now is that I want to make that no matter what it does not lock the database in case of error.... :)

    Thanks again.


    Pedie
    Wednesday, September 07, 2011 6:54 PM
  • If you use ServerSide, and wish to update the data, then the static is promoted to a keyset:

    adOpenStatic adLockOptimistic
    adLockPessimistic
    adBatchOptimistic
    For all other lock types, a Keyset cursor is returned.

     

      And then, the relevant locking mechanic is, for a keyset, occurs as explained in the reference:

     

    adOpenKeyset adLockOptimistic The Recordset object has an updatable, scrollable cursor. You can see updates and deletions, but not insertions made by other users. The record is not locked to save updates to the current record until the Update method is called, or when you move to a different record.
    adOpenKeyset adLockPessimistic The Recordset object has an updatable, scrollable cursor. You can see updates and deletions, but not insertions made by other users. The record is locked when the first field is modified.

     

    "

    • Marked as answer by Pedie Nz Thursday, September 08, 2011 7:52 PM
    Wednesday, September 07, 2011 10:10 PM
  • Okay. Thank again soo much for helping me out :)
    Pedie
    Thursday, September 08, 2011 7:52 PM