Search code examples
c#ms-access-2010oledb

Set Access 2010 record locking to optimistic locking through OleDb (C#)

I am communicating with Access 2010 through OleDb in C#. How can I set the locking type to use optimistic locking from C#?

In case anyone wonders, I am using this connection string to connect to my Access database:

// Set the data source string.
_sqlCon = new OleDbConnection(
    String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=True", databasePath)
);

Update #0

I found some information about adLockOptimistic from ADO on the Microsoft MSDN website (https://msdn.microsoft.com/en-us/library/ee252458(v=bts.10).aspx) which might be totally irrelevant because I am using OLE.

Update #1

I tried to add the following to the connection string:

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=True;LockType=3;"
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=True;Lock Type=3;"
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=True;LockType=adLockOptimistic;"
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=True;Lock Type=adLockOptimistic;"

Which all result in the following exception:

Could not find installable ISAM.

I'd expected it not to work, but hey, who knows if you don't try.

Update #2

If it is possible to define this property in my Access database, that solution is perfectly fine too! However, this should be a database setting and not a client specific setting and if it is possible from C#, that method has my preferences.

Contextual information:

I am doing a concurrency stress-test on my Access database (over a network). I have about 20+ clients (little C# programs) that are reading the same record from the same table as fast as possible for each client. This gives a lot of database is in 'Admin' mode by .. exceptions and I catch these exceptions in my little C# client. I want to be able to read these records at the same time to tackle a bigger problem in one of my applications. I can easily solve this problem by switching to a "real" database system but this is unfortunately not possible.

If the problem above is solved in any other way, I am still really interested in how to tell Access to use different locking types (from OleDb/C#).

Solution

  • Gord Thompson wrote:

    Have you tried using an OleDbTransaction with System.Data.IsolationLevel.ReadUncommitted?

    Locking and transaction isolation levels are interrelated. For example, the description for IsolationLevel.ReadUncommitted says that "no shared locks are issued and no exclusive locks are honored" (ref: here). I have not seen any options in System.Data.OleDb that seem to match the adLockOptimistic option for (COM) ADO Recordset objects.