Mutiuser editting database

[ltlearn]

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 ?

[Dr_Evil]

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.

[ltlearn]

Thanks. I am using the MS Access 2000 database. Thanks again....

[marex]

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

[Thom]

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!

[marex]

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

[ltlearn]

Would someone share the sample code of the recordset locking and unlocking for the multi user editting database ?

Thanks.

[theroper]

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

[Thom]

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!

[marex]

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

[Thom]

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!

[Edneeis]

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/downloads/download.asp?ID=085 or check out the modConnections.bas http://msdn.microsoft.com/code/default.asp?URL=/code/sample.asp?url=/msdn-files/026/000/121/MsdnCompositeDoc.xml.

[hotsauce]

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!