CODEWITHSUNDEEP
ms-accessodbc
Agendum
Agendum

Reputation: 2011

Microsoft Access ODBC driver causes access violation when Access SQL functions are used

I am using the Microsoft Access ODBC driver (installed by AccessDatabaseEngine_X64.exe) on my local machine to run "SQL" statements on an older Access database. Everything works fine here.

But now I am deploying it to a Docker container (using mcr.microsoft.com/windows:1809-amd64) as the base image. My dockerfile installs .NET Core and also AccessDatabaseEngine_X64.exe. But when I run my app on the container, it executes hundreds of "SQL" statements just fine but then suddenly gets an AccessViolationException. I eventually figured out after running the same transaction script over and over that it was always crashing on the same query -- and the query contains IsNull().

I confirmed the driver files on the container match that of my local machine (both 64-bit only). For testing, I am using the exact same database on the local machine and container -- but that doesn't really matter because the database can be empty to get the access violation.

I've reduced the test application down to this simple program:

const string connectionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=db\Test.mdb";
const string sql = "SELECT IsNull(null)";
var odbcConnection = new OdbcConnection(connectionString);
odbcConnection.Open();
odbcConnection.Execute(sql); // Dapper

On my local machine it works fine, but in the container this crashes with an AccessViolationException. Here is the exception right from the container output:

Fatal error. System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
   at Interop+Odbc.SQLExecDirectW(System.Data.Odbc.OdbcStatementHandle, System.String, Int32)
   at Interop+Odbc.SQLExecDirectW(System.Data.Odbc.OdbcStatementHandle, System.String, Int32)
   at System.Data.Odbc.OdbcStatementHandle.ExecuteDirect(System.String)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(System.Data.CommandBehavior, System.String, Boolean, System.Object[], SQL_API)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(System.Data.CommandBehavior, System.String, Boolean)
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(System.Data.IDbConnection, Dapper.CommandDefinition ByRef, System.Action`2<System.Data.IDbCommand,System.Object>)
   at Dapper.SqlMapper.ExecuteImpl(System.Data.IDbConnection, Dapper.CommandDefinition ByRef)
   at Dapper.SqlMapper.Execute(System.Data.IDbConnection, System.String, System.Object, System.Data.IDbTransaction, System.Nullable`1<Int32>, System.Nullable`1<System.Data.CommandType>)

I want to clarify that I am running hundreds of other queries just fine on the container, but it always fails when IsNull() is anywhere in the command text.

So I was curious and tried some other Microsoft Access functions, like Now() and IsNumeric(1030). Those also throw AccessViolationException!

I am really struggling with how to continue to resolve this problem. Why is the driver behaving differently? Doesn't the driver support Microsoft Access functions, or is there a dependency I need to add to get full Microsoft Access support?

Thanks

Upvotes: 0

Views: 1191

Answers (1)

Agendum
Agendum

Reputation: 2011

The reason I was getting the Access Violation is because I needed to install the Microsoft Access Runtime. Both the Access Database Engine and Microsoft Access Runtime are both required -- if only the database engine is installed connections to the MDB work but you cannot do anything fancy.

Now properly installing the Microsoft Access Runtime on a Docker container was no small feat. For that, there is a verbose answer to this question: How to install Access Runtime on a Docker container?

I want to add that I did not need any other dependencies to get this to work end-to-end. Not VC Runtime and not the .NET Framework.

Hope this helps somebody else in the future.

Upvotes: 1

Related Questions