Access 2007 - "Failed to update, currently locked"
Sorry for my bad english at first, but I'll try my best.
I am currently programming a .net application using Access 2007 as a data store.
In a nutshell: I have two streams. One thread inserts a row with a transaction into the table. Another thread is updating many lines at regular intervals.
Topic 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();
}
Topic 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 insert a lot of records, I get a System.Data.OleDb.OleDbException that says "Failed to 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;"
without affecting the behavior of my application. I decided to use these transactions to avoid chaotic inserts and updates.
Is there a workaround? What am I doing wrong? Can I insert my transactions into some kind of transaction queue in Access? Why doesn't Access do it on its sheet?
Stefan Gruber asked:
Can I insert my transactions into some kind of transaction queue in Access? Why doesn't Access do it itselft?
I think you don't understand the Jet database engine. First, a clarification: MS can use "MS Access" as the name in it for ODBC and OLEDB connection strings, but in this case you are not using Access at all - just use the default database engine, Jet.
Jet is not a server database engine. That is, there is no server process between the clients and the MDB file where the data is stored. All "users" of the MDB file access it through the file system. To control multi-user access, there is a lock file (LDB file) that keeps track of which tables / records are locked and which locks. Jet examines this LDB file to determine what it can and cannot do.
Now, since there is no process on the server to manage all data interaction on disk, there is no way to ever collect requests to access the MDB file. Your application should do this on its own.
If this is unsatisfactory, you are using the wrong data store.
source to share