AccessViolationException when filling DataTable

Question

I am facing a strange problem with an tool, that should be able to read data from an Excel file and write it into a SQL-Database.

            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                        "Data Source=" + filename + ";" +
                        "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
        DataTable dt;
        OleDbDataAdapter dataAdapter;
        dataAdapter = new OleDbDataAdapter("SELECT * FROM [" + sheet + "$]", strConn);
        dt = new DataTable();
        try
        {
            dataAdapter.Fill(dt);   //Programm reagiert nicht mehr
        }
        catch(Exception ex)
        {
            Logger("Problem filling Adapter: " + ex.ToString());
            return null;
        }

The following exception occurs at dataAdapter.Fill(dt)

Problem filling Adapter: System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at System.Data.OleDb.DataSourceWrapper.InitializeAndCreateSession(OleDbConnectionString constr, SessionWrapper& sessionWrapper) at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ExcelExportWindowsForms.Program.ReadExcel(String filename, String sheet)

The interesting thing is, that this works perfectly on my machine (Excel 2013), but does not on a server with Excel 2003. Could this be the reason?

Answer 1

(from comment)

In your comments you mention you installed the latest AccessDatabaseEngine. I've had problems with that as well (at the time I found lots of references of people with the same problem, but don't have those links anymore). I had to use the 2010 version. Might be something to try if all else fails (you probably have to uninstall the other version first). The reason is unclear (to me anyway), but it may have something to do with x86 v x64 installations.

Answer 2

Using Microsoft.ACE.OLEDB.16.0 (trying both 32 and 64 bit), the System.AccessViolationException was still an issue reading an MS Access file multiple times on Windows Server 2012 (64 bit). Adding the parameter OLE DB Services = -1 to the connection string seemed to solve the issue for me. The parameter is explained on MSDN and the fix was suggested on CodeProject

Answer 3

For me, the problem was that the file it was trying to access was locked by Excel somehow, because I was neither able to delete or move the file. I first tried to open Task Manager to halt Excel, but couldn't find Excel there, so I just restarted the computer instead, which worked like a charm.