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?

You are using an excel 2013 connection string for excel 2003, yes, that could be the reason. Try `Extended Properties="Excel 8.0;` — HoneyBadger, Dec 22 '15 at 13:48
There also may be only the older versions of the data access drivers available. You many need to change the provider as well. Check out connection strings https://www.connectionstrings.com/excel-2003/ for all the possibiliies — dbugger, Dec 22 '15 at 13:49
It seems connection is not closed properly and it tries to make a second connection at the same time. — Orkun Bekar, Dec 22 '15 at 13:53
Is there a 32/64 bit conflict maybe? Can you check [this answer][(http://stackoverflow.com/a/14982382/578411). — rene, Dec 22 '15 at 14:02
This might help: https://social.msdn.microsoft.com/Forums/en-US/8789ea67-fbc5-4a7b-a4eb-d4a8a050d5c1/attempt-to-read-or-write-protected-memory-this-is-often-an-indicating-that-other-memory-is-corrupt?forum=vbpowerpacks — sr28, Dec 22 '15 at 14:05
dbugger I tried every connection string. Did not change anything either. @OrkunBekar what should I do to test this? rene I don't really understand what this has to do with my problem. — xileb0, Dec 22 '15 at 14:14
Btw i am pretty sure that the Provider is correct, since I just installed the newest AccessDatabaseEngine — xileb0, Dec 22 '15 at 14:40
I'm not sure but maybe the file which you are trying to write is open or it is used by another process. Close it and try again. — Orkun Bekar, Dec 22 '15 at 14:49
The office oledb data access components are notoriously persnickety. Have to be aware of the installed data access libraries, the selected platform (32 v 64 bit), file permissions for the account trying access the file, the connection string, and whether or not the file is being held by another process. — dbugger, Dec 22 '15 at 14:50
OrkunBekar I think we can rule out that it is used by another process. I just checked every process and then I took a completely new file, that has definitely never been opened before. @dbugger But this does not tell me any way to solve this problem :( — xileb0, Dec 22 '15 at 15:02
@xileb0, It's painful, but you need to walk through each part of the problem, validate your assumptions, eliminate the variables, and isolate the root problem. As you have seen there is no silver bullet. — dbugger, Dec 22 '15 at 15:08
@dbugger And what about using a different way to fill my DataTable variable? Or is there no workaround? — xileb0, Dec 22 '15 at 15:11
Check out the ClosedXML library, it doesn't work with old Excel formats, but if you are using xlsx files it plays with them quite nicely. — dbugger, Dec 22 '15 at 15:14
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). — HoneyBadger, Dec 22 '15 at 16:49
@HoneyBadger you are my hero! Actually works with version 2010! Thank you SO MUCH! Merry Christmas! Write it as an answer pls :) — xileb0, Dec 23 '15 at 07:04

score 3 · Accepted Answer · answered Dec 23 '15 at 08:23

(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.

score 1 · Answer 2 · answered Aug 08 '18 at 18:19

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

score 0 · Answer 3 · answered Aug 10 '20 at 05:41

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.

AccessViolationException when filling DataTable

3 Answers3