Results 1 to 13 of 13

Thread: Mutiuser editting database

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2000
    Location
    Posts
    124
    How do I take care of the issue of database for data integrity as it has the potential of editted by more than one user ?

  2. #2
    Lively Member Dr_Evil's Avatar
    Join Date
    Mar 2000
    Location
    Columbus, OH
    Posts
    105
    Using Recordset Locking will solve your multiuser problem.
    Which type of locking you use depends on which Data Access method you are using and what Database you are using. If you provide more info I can give you a more detailed example.
    Dr_Evil
    Senior Programmer
    VS6 EE
    VS.NET EA

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Nov 2000
    Location
    Posts
    124
    Thanks. I am using the MS Access 2000 database. Thanks again....

  4. #4
    Fanatic Member
    Join Date
    Nov 2000
    Location
    Worldwide in the Sun
    Posts
    566
    To Dr_Evil

    I'm very intrested to see your code example on the
    subject of record locking.
    I'm trying for days to solve my problem but nothing works.
    Using adOptimistic or adPessimistic should do the trick.
    In DAO he locks a page (2k) but in my case instead of
    the page the whole table has been locked.
    In ADO the locking does nothing at all.

    I'm using VB6 SP4, Windows 2000, Access 2000.
    I have two versions, one in DAO the other in ADO.
    So maybe you can help.

    Greetings
    Ray
    Ray

  5. #5
    Lively Member
    Join Date
    Feb 1999
    Location
    Austin,TX,USA
    Posts
    98
    Hi all! I've got a similar problem! I'm using ADO 2.50, VB6.0 SP4 and Access2000. When I've got 10-15 people taking a test & then wanting to save their results to the database, I need to make sure that ONLY 1 person at a time can INSERT his results into the table. So I basically want the FIRST person who clicks on the SAVE button to lock the table where the test results are stored.

    Can that be done with a adoConnection using INSERT? i.e. how do we set locking permissions when using the
    cnn1.Execute(INSERT blah blah)?? Marex are you saying that locking doesn't work AT ALL with ADO?? Thanks for ANY help!



  6. #6
    Fanatic Member
    Join Date
    Nov 2000
    Location
    Worldwide in the Sun
    Posts
    566

    TO Thom

    No misunderstanging about locking in ADO.
    It should work on ADO but I must made some error in
    coding.
    I've changed the application from DAO to ADO and maybe
    I'm doing something wrong with these lock settings.
    Therefore my question for some coding examples.

    Cheers
    Ray
    Ray

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Nov 2000
    Location
    Posts
    124
    Would someone share the sample code of the recordset locking and unlocking for the multi user editting database ?

    Thanks.

  8. #8
    New Member
    Join Date
    Aug 2000
    Posts
    15
    Why not have a field in the database such as "editor"
    When you go to do an update
    you first check to see if the field is empty...
    then you populate it with your user name
    You can then hold the lock for as long as required and not have a live connection to the Database

    That way you are not locking other records

  9. #9
    Lively Member
    Join Date
    Feb 1999
    Location
    Austin,TX,USA
    Posts
    98
    After doing some reading/research you MAY want to consider using an ADO connection and its Mode setting to lock the tables/DB. I'm still working this out but I THINK the following would work to allow:

    dim cnn1 as ADODB.Connection
    Set cnn1 = New ADODB.Connection
    cnn1.ConnectionTimeout = 30
    ' WE DON'T WANT OTHERS WRITING HERE WHILE WE ARE!!
    cnn1.Mode = adModeShareDenyWrite
    cnn1.Open g_strConnection

    I ALSO ran across this tidbit in MSDN-but I'm not sure exactly how it works.

    '******** Remote Data Service Usage
    When used on a client-side Connection object, the Mode property can only be set to adModeUnknown.

    Does this mean we can't use disconnected recordsets and use the Mode properties?? If so, the above code wouldn't make a difference. But if not-then you should be able to have a user connect from a client PC and LOCK the DB while doing whatever important operation needs to be done...I'm wondering if this would be easier than theroper's way? If ANYONE has more info about this Mode stuff-please share it! Thanks!


  10. #10
    Fanatic Member
    Join Date
    Nov 2000
    Location
    Worldwide in the Sun
    Posts
    566
    Hi

    Take a look at this info.

    FIX: ADO Client Cursors Report LockType = adLockPessimistic

    The information in this article applies to:

    ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1
    Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

    SYMPTOMS

    ActiveX Data Objects (ADO) incorrectly reports that a client-side recordset opened with a LockType of adLockPessimistic was opened with pessimistic locking.

    The ADO client cursor engine does not support pessimistic locking. If you ask ADO for a pessimistically locked client-side recordset, you actually receive an optimistically locked recordset instead, even though ADO did not change the value of the LockType property to adLockOptimistic.

    With MDAC 2.1 sp2 the locktype is now returned as adlockbatchoptimistic(4).

    STATUS
    Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.
    This bug has been fixed in MDAC 2.1 Service Pack 2.

    MORE INFORMATION
    Steps to Reproduce Behavior

    1.Open a Standard.exe project in Visual Basic, and add a reference to the Microsoft ActiveX Data Object Library.

    2.Copy and paste the following code into the form code window:

    Option Explicit
    Dim myconn As New ADODB.Connection
    Dim myrs As New ADODB.Recordset

    Private Sub Form_Load()
    Dim strConn As String

    'Modify the connection string to point to your NWind.MDB file.
    strConn = "Provider=MSDASQL;" & _
    "Driver={Microsoft Access Driver (*.mdb)};" &; _
    "DBQ=D:\vb5\Labs\LAB10\NWind.MDB"

    myconn.CursorLocation = adUseClient
    myconn.Open strConn

    'Request a pessimistically locked cursor.
    'ADO's client cursor engine only supports optimistic locking.
    myrs.Open "SELECT * FROM Customers", myconn, _
    adOpenStatic, adLockPessimistic, adCmdText
    MsgBox "MyRS.LockType = " & myrs.LockType & vbCrLf & _
    "adLockPessimistic = " & adLockPessimistic & vbCrLf & _
    "adLockOptimistic = " & adLockOptimistic
    myrs.Close

    3.Run the form and note the output of the message boxes.

    REFERENCES
    ADO 2.0 documentation
    Additional query words:
    Keywords : kbADO150bug kbADO200 kbADO200bug kbDatabase kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2fix kbMDAC210SP2fix
    Version : WINDOWS:1.5,2.0,2.1,5.0,6.0
    Platform : WINDOWS
    Issue type : kbbug



    Cheers
    Ray
    Ray

  11. #11
    Lively Member
    Join Date
    Feb 1999
    Location
    Austin,TX,USA
    Posts
    98
    Thanks for the info Marex! I was scrolling thru old posts & found this 1 by JHausmann:

    1) create a "lock" table that has 1 entry
    2) when you want to write to the DB, the program attempts to lock the sole record. If it cannot, someone else is using the database-if the program CAN lock the record-proceed with your work.

    Dr_Evil is this what you were talking about? What I'm trying to do is make sure that when an Admin is updating/building a test NO ONE can get ANYTHING from that table til he's done. Would using adLockPessimistic for the recordset ensure that? Also when I have a User saving his test results to a table(by Inserting the info as new records) I don't want ANY other User to be able to save their results UNTIL the 1st one is done-will adLockPessimistic solve this as well? I apologize for the confusion but I can't find ANYWHERE on MSDN the specifics of how to prevent multiple users from adding to a table in DB at once! Thanks for ANY help!

  12. #12
    Your Ad Here! Edneeis's Avatar
    Join Date
    Feb 2000
    Location
    Moreno Valley, CA (SoCal)
    Posts
    7,339
    Check out Microsoft's example it uses business rules to reconcile the differences if a user changes a record at the same time as another. Read the documentation http://msdn.microsoft.com/vbasic/dow...oad.asp?ID=085 or check out the modConnections.bas http://msdn.microsoft.com/code/defau...positeDoc.xml.

  13. #13
    Member
    Join Date
    Oct 2000
    Posts
    35

    Exclamation Thom

    What do you mean create a table that is locked. I have had my application for 3 years. All the suddenly the db is very slow. I have tried compacting and repairing the access db. Since it became really slow two weeks ago it takes a good 3-5 seconds to update my "sale" table. Since it takes so long, two or more users can't close a "sale" at the same time or it will lock up. It use to not interfere or lock up because it only took less than a second to close the sale, but since it takes longer it is know locking up the table for up to 5 seconds. Anybody, please help!

Posting Permissions

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



Click Here to Expand Forum to Full Width