Access DB Transaction Insert limit

Posted by user986363 on Stack Overflow See other posts from Stack Overflow or by user986363
Published on 2013-11-01T11:32:31Z Indexed on 2013/11/02 9:54 UTC
Read the original article Hit count: 215

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about c#

Related posts about transactions