You have to make sure of the following:

Set cn = New ADODB.Connection

With cn
    .Provider = "microsoft.jet.oledb.4.0"
    .ConnectionTimeout = 15
    .CursorLocation = adUseServer
    .Mode = adModeReadWrite
    .ConnectionString = "Data Source=C:\Test\Test.mdb;"
    .Properties("Jet OLEDB:Database Locking Mode") = 1
    .Open
    .Properties("Jet OLEDB:Lock Delay") = 1
    .Properties("Jet OLEDB:Lock Retry") = 10
  End With

  With rs
      Set .ActiveConnection = cn
    .CacheSize = 1
    .CursorLocation = adUseServer
    .CursorType = adOpenKeyset
    .LockType = adLockPessimistic
    .Properties("Jet OLEDB:Locking Granularity") = 2
    .MaxRecords = 0
    .Source = "TEST"
    .Open , , , , adCmdTableDirect
End With

The only way I have Row Locking working is when EVERY connection is
open with mode = adModeReadWrite. If somewhere in my program I want to look
at a table (view only) and set mode = adModeRead then locking is set to page
locking. With some research, I found that the FIRST recordset to open the
table sets
the locking scheme for EVERYONE. I can not seem to tell A2k that the default
is row locking.
In every case I have set my program to use LockType= adLockOptimistic,
but this one database must have row locking.

It seems wasteful to open every query and table as mode = adModeReadWrite,
But with the above, I got Row level locking to work. Also, the database MUST
be in the Access 2000 format; it will not work with a Access 97 MDB

Darryl

Hi,
I am trying to use the record level locking feature in Jet 4.0.  Here
is my code in Visual Basic 6.0.

In my test database "test.mdb", I have a table called "mytable" with a
field called "0".  The records in this table are 1, 2, 3.

***** beginning of test exe 1 ******

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\test.mdb;"
conn.Properties("Jet OLEDB:Database Locking Mode") = 1
conn.Properties("Jet OLEDB:System Database" = "c:\\test.mdw"
conn.Open
With rst
    .CursorLocation = adUseServer
    .CursorType = adOpenKeyset
End With

rst.Open "mytable", conn, adOpenKeyset, adLockPessimistic,
adCmdTableDirect
rst.Properties("Jet OLEDB:Locking Granularity") = 2

Do While 1
rst.MoveFirst
rst.Fields("0").Value = 1
rst.Update
Loop

***** end of test exe 1 ****

In my second test exe, everything is same except this:

....
....

Do While 1
rst.MoveLast
rst.Fields("0").Value = 3
rst.Update
Loop

***** end of test exe 2 ****

What I expected to see if that both exe will continue running with no
problem since I told ADO to use record level locking.  But, this is not
the case.  My second test exe will always fail complaining about the
record being locked.  I think this is because ADO is still using page
level locking.
If I open "test.mdb" in MS ACCESS first, both exe will run happily.

So, my question is: Am I doing something wrong? or Record level locking
is only supported if you run your code inside MS Access.

Thank you for any input.

Sent via Deja.com http://www.deja.com/
Before you buy.