Setting Database Page Locking in DAO3.6

[Francis]

Can anyone tell me how to set Page-Level Locking for an MDB (Access 2000) via VB6?
I want to do this to optimise multiuser performance
Thanks

 

[strongm]

Assuming that you are using ADO and have a connection called con, try

con.Properties("Jet OLEDBatabase Locking Mode") = 0 ' use 1 for record level locks

before Opening the connection

 

[strongm]

i.e you can't do it purely in DAO - at least, that I am aware of. The trick is to open an ADO connection to the DB as per the hint above, leaving that connection open, and then doing your subsequent work through DAO

 

[Francis]

Thanks. Unfortnuately changing to ADO at this point is a huge effort.

 

[SBerthold]

>set Page-Level Locking

DAO already uses by default Page level locking, either pessimistic which is as soon as a record goes into the edit mode, or optimistic, only when the record update is actually being committed.

You are looking for optimistic locking in multi-user usage.

So, if you really meant Row level locking, then do as strongm says. You do not need any big changes, just one connection object along side of your DAO:

http://support.microsoft.com/kb/306435/en-us

However, you may end up more often with a corrupted database.

 

[Francis]

Thank you very much I will try it out.

 

[BobRodes]

Note: Record locking isn't supported in JET versions prior to 4.0.

 

[SBerthold]

Yes, and not supported through the DAO 3.6 interface to the JET 4 dbms, which is the same engine used by the OLEDB JET 4 Provider supporting it.

Therefore, using the method mentioned by strongm and MS works, because it is not DAO or ADO implenting the locks, but JET 4, where the fuctionality was built into.

I am just not sure, because I never tested it, that if the database is still prior JET 4 format, but accessed through JET 4, if row locks will still work.

 

[BobRodes]

I believe that you have to have both JET 4 and Access 2000 or higher.