Hello. First sorry for my bad english but I'll try my best.
Right now I'm programming a .net application using Access 2007 as datastore.
In a nutshell: I have two threads. One thread inserts a row with a transaction into a table. The other thread updates many rows in constant intervals.
Thread 1
Database db = _loggingDatabase;
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction txn = conn.BeginTransaction();
try
{
string qryInsert = "Insert this";
DbCommand cmdIns = db.GetSqlStringCommand(qryInsert);
db.ExecuteNonQuery(cmdIns, txn);
txn.Commit();
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
finally
{
conn.Close();
}
Thread 2
Database db = _loggingDatabase;
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction txn = conn.BeginTransaction();
try
{
string qryUpdate = "Update that";
DbCommand cmdUpdt = db.GetSqlStringCommand(qryUpdate);
db.ExecuteNonQuery(cmdUpdt, txn);
txn.Commit();
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
finally
{
conn.Close();
}
If I trigger to insert many records I'll get an System.Data.OleDb.OleDbException which says: "Could not update; currently locked". I tried to change the Connectionstring to
connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=datastore.accdb; Jet OLEDB:Database Locking Mode=1;"
with no effects on my application behavior. I descided to uses those transactions to avoid chaotic inserts and updates.
Is there a workaround? What am I doing wrong? Can I insert my transactions to some kind of transaction queue into Access? Why isn't Access doing this by itselft?