Row-level locking in Access 2000

First, I start Access 2000 and open an .mdb database( with option Default record locking set to "Edited record" and option
"Open database using record-level locking" set to True.
Second, I run 2 exe copies of a program written with VB6 in which I open (in pessimistic mode) a table in the above mentionned database (using DAO 3.6 objects)
and try to edit the same record. I can see that row-level locking works fine.
Now I close all running applications (Access 2000 included) , start again these 2 exe copies but without opening in advance this database with Access 2000 application; now row-level locking does'nt work. (instead,I get page-level locking)
My question: how can I get row-level locking
without opening database before with Access?

Thanks in advance.

[Clunietp]

Hey Claude

I have found a parameter that you can use in your connection string:

Jet OLEDB: Database Locking Mode = 1

By default, it is 1, and I don't know what any other values can be (i'm assuming 2 and up, but I don't know what they mean!)

By default, your connection may be using locking mode 1, instead of the number for row level locking.

Please test this out, and let us know. I'm sure we would all be grateful to have this little tidbit of info for the future.

I cannot find any information about this parameter anywhere on the web or MS site.

Thanks

Tom

[This message has been edited by Clunietp (edited 12-22-1999).]

[Clunietp]

Anyone know anything about this?

[Gerald]

Tom,

From what I've been able to find the ADO "OLEDB: Database Locking Mode" property can be either 0 or 1. When the mode is set to zero it uses the mode used in previous versions of the Jet database engine. When set to one, it uses the new method which allows row level locking. What is being demonstrated by Claude's example is that the first connection made to the database will set the mode used by all subsequent connections regardless of what mode is explicitly requested in application code. This is exactly what is happening with Claude when he starts Access 2K first before running his VB app and vice versa. So I guess the question remains--how to you tell DAO 3.6 to use row level locking?


Gerald

[Clunietp]

Gerald

So you're saying that ADO (with Locking Mode = 1) will use the row level locking features, but the problem is making that happen with DAO 3.6?

Ya know, as I read over the initial post, I should have saw that he was using DAO, not ADO. Oh well! Thank you anyways gerald. Please let me know if what I think you said was true

Tom

[This message has been edited by Clunietp (edited 12-24-1999).]

[Clunietp]

After careful study of the DAO 3.6 help file, the default recordset LockEdit is pessimistic, which enforces PAGE LEVEL locking. Optimistic locking also enforces, yes, you guessed it, PAGE LEVEL locking. Seems to me that you can only other way to implement row level locking is via the Jet OLEDB 4 provider. (ADO baby!)

This would make sense, since M$ did not make many improvements to DAO 3.6, they just made it compatible with Access 2K databases so existing DAO projects can use this libary instead of converting all code to ADO.

[This message has been edited by Clunietp (edited 12-24-1999).]

[Gerald]

That's pretty much the way I see it Tom--DAO never did support anything other than page level locking. Maybe DAO 3.6 has a new DBEngine.SetOption setting that allows record level locks? I doubt it. Its' like you said, M$ more than likely did not add any new functionality to DAO 3.6.


Gerald