Intermittent "System resource exceeded" exception for OleDB connection to Microsoft Access data file

Question

I'm experiencing a seemingly random "System resource exceeded" exception when running my code. The idea behind my program is that a third party piece of software is continuously writing data to a Microsoft Access database file (.res) - about every 30 seconds my code reads the data from this file, does some operations on it, and writes the results to our database. Unfortunately I cannot change the way the third party software writes data to files, I am stuck with Access data files.

This error occurs both on the production system running the WinForms program installed through Click-Once publishing as well as in a console test program on my development system. I get the exception even when running a query that returns a single integer and no other program or thread is touching the file which is located on my local disk.

Exception information:

System.Data.OleDb.OleDbException (0x80004005): System resource exceeded.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteScalar()
...

Example code that reproduces the problem:

string connectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\datafile.res";
string commandText = "SELECT MIN(Data_Point) FROM Channel_Normal_Table WHERE Test_ID = 1";

int connectionCounter = 0;            
object result;

while (true)
{                
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        connectionCounter++;

        using (OleDbCommand command = new OleDbCommand(commandText, connection))
        {
            result = command.ExecuteScalar();
        }

        connection.Close();
    }
}

Unfortunately the exception is not deterministic - I have seen it occur anywhere from the 4th command execution to the 6149th on the same file with the same code. It always occurs on the command.ExecuteScalar() line. If there is a resource leak in this code, please help me find it.

I have tried installing the hotfix found at http://support.microsoft.com/kb/2760394 (and made the required registry change), but it does not solve the problem. Any suggestions would be appreciated and aggressively pursued.

This is running on Windows 7, C# 4.0 (console and WinForms), 4 GB RAM

Answer 1

A couple of things I would try in your case:

1. Since the user of the database is the application only, I would open the database in exclusive mode, this will help the driver get rid of the overhead of managing lock files (and should speed-up access to the db too).

// Share Mode=12 - exclusive mode (16 for multi-user)
string constr = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                 "Mode=12;Data Source = C:\datafile.res;user id=;password=;";

1. Open a connection to the Access database when your program start, and leave it open until the application closes.
   In tight loops, lock-file issues creep up and cause all sorts of strange issues that are hard to debug.
   Just have a dummy table with a single record in the Access database, then open that table to read the record, but keep a permanent reference to that connection:

private OleDbCommand PermanentCommand;

void KeepLinkOpen() {
   if (PermanentCommand == null || 
       PermanentCommand.Connection == null || 
       PermanentCommand.Connection.State == System.Data.ConnectionState.Closed) {

     OleDbConnection conn = new OleDbConnection(connectionString);
     conn.Open();
     PermanentCommand = new OleDbCommand("SELECT * FROM DummyTable", conn);
     PermanentCommand.ExecuteReader(System.Data.CommandBehavior.Default);
  }    
}

void Disconnect() {
  if (PermanentCommand != null) {
      if (PermanentCommand.Connection != null) {
          PermanentCommand.Connection.Close();
      }
      PermanentCommand.Dispose();
      PermanentCommand = null;
  }
}