I have a simple multi-threaded .NET app which inserts rows into an MS Access 2010 DB using ACE.
It only does INSERT operations.
Because MS Access does page-level locking on inserts, I sometimes get locked out.
Is there a way to change this locking type? Or is there a "danger mode" where I can tell it to ignore locks entirely and just potentially make new pages if it finds a locked one?
EDIT: Is there no way at all to control locking here? The hack I have which works thus far is by maintaining a single, shared connection for these transactions then the connection objects just queues up the inserts. This works but I don't like this connection just floating around.
What interface are you using to updated the database? OLEDB? If so, this article seems to answer the question:
PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
It seems to be a matter of setting a property of the connection object:
Set the Connection object's dynamic Properties("Jet OLEDB:Database Locking Mode") to 1.
For what it's worth, I would not consider Jet/ACE to be a very suitable data store for this kind of thing, but it would depend on the frequency of the inserts.
10 an hour, no problem.
10 a minute, I'd switch to a different database.
BTW, using a single connection is the preferred method of working with a Jet/ACE data store because of the overhead of recreating the locking file each time you re-open the connection.