Question How to set Record Level Locking in MS Access in VB 6.0 using ADO Object through ODBC

[Nishidh]

How to set Record Level Locking - MS Access 97 VB 6.0 through JET OLEDB 4.0

Hi,

I am accessing MS Access 97 using VB 6.0 with ADO object through JET OLEDB 4.0.

I have 2 different applications. One for Data Entry to enter the data in MS Access Database. Another application is Front End to view the data.

I was facing problem like "Could not update; currently locked by user 'Admin' on machine 'MachineName'" at DataEntry Application.

I read one article in that it is mentioned like "If Access database or Table is opened using page level locking, subsequent call to open the same table would be in Page Level Locking; and it is open in Record Level Locking, subsequent Open call to open the same would be in record level locking"

So I had open the recordset as follows in update statement for "TableA" fired by Data Entry application; and in Front End application, where same table "TableA" is accessed using Select statement. So I make sure that if Front End application is using only "Select Statement" it would open in Record Level Locking , so Update statement in Data Entry application can open the same table in Record Level Locking.

Approach is as follows : ->

Open Connection like:

ConnObjectName.ConnectionString = "ConnectionString using Jet .OLEDB.4.0 and Access Database path"
ConnObjectName.Properties("Jet OLEDBatabase Locking Mode") = 1
ConnObjectName.Open

and Recordset like:

rs.ActiveConnection = ConnObjectName
rs.Properties("Jet OLEDB:Locking Granularity") = 2
rs.LockType = adLockPessimistic
rs.CursorType = adOpenKeyset
rs.Source = strqry
rs.Open

After implementing the above approach, I am facing the same problem
"Could not update; currently locked by user 'Admin' on machine 'MachineName'" at DataEntry Application

Please help me resolve the above problem? Also let me know MS Access 1997 gives the support for Record Level Locking.

Thank you very much in Advance. I would really appreciate your efforts in advance.

Thanks,
Nishidh