Is there a limit to the amount of inserts you can do within an Access transaction before you need to commit or before Access/Jet throws an error?
I'm currently running the following code in hopes to determine what this maximum is.
OleDbConnection cn =
new OleDbConnection(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\myAccessFile.accdb;Persist Security Info=False;");
try
{
cn.Open();
oleCommand = new OleDbCommand("BEGIN TRANSACTION", cn);
oleCommand.ExecuteNonQuery();
oleCommand.CommandText =
"insert into [table1] (name) values ('1000000000001000000000000010000000000000')";
for (i = 0; i < 25000000; i++)
{
oleCommand.ExecuteNonQuery();
}
oleCommand.CommandText = "COMMIT";
oleCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
try
{
oleCommand.CommandText = "COMMIT";
oleCommand.ExecuteNonQuery();
}
catch{}
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
}
The error I received on a production application when I reached 2,333,920 inserts in a single uncommited transaction was: "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry". Disabling transactions fixed this problem.
