0
votes

Can't connect OdbcConnection Microsoft Access Driver - After publish System.Data.Odbc.OdbcException (0x80131937)

After publishing the application I lose OdbcConnection/Command functionality.

The code below is where the application errors out when published. In development all of this works.

    //Connect to access .mdbcd 

    var conn = new OdbcConnection(@"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\wwwroot\mo22\storesales.mdb");
    conn.Open();

    //Clear out table
    OdbcCommand Com = conn.CreateCommand();
    Com.CommandText = "DELETE FROM tblAcctData";
    Com.ExecuteNonQuery();

    returnList.ForEach(el =>
    {
        OdbcCommand thisCmd = conn.CreateCommand();
        thisCmd.CommandText = "INSERT INTO [tblAcctData] ([Store], [ProcDate], [Name], [Amount]) VALUES('" + el.Store + "', #" + el.proc_date + "#, '" + el.Name + "', " + el.amount + ")";
        thisCmd.ExecuteNonQuery();

    });



    //Close connection
    conn.Close();

So far I have -added Microsoft Access to the server.
-added 32-bit and 64-bit Drivers in System Data Sources. -Edited the connection string with additional credentials. -Tried ExtendedAnsiSQL=1; in the connection string.

When I do add the 64 bit driver (Microsoft Access Driver (.mdb,.accdb)) in system data sources I don't get an error in the log anymore and I get this error on the front end.

t {headers: e, status: 502, statusText: "Bad Gateway", url: "http://apps.o.com/mochub2/api/acctlegenddata/getdata?stores=01&date=9/2/2019", ok: false, …}

So I feel like I am kind of getting somewhere because there is no odbc error. Just a error saying my gateway is bad.

Does anyone know what the next steps would be?

Here is the Error without the 64 bit Microsoft Access Driver.

System.Data.Odbc.OdbcException (0x80131937) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at mocHub2.Controllers.AcctLegendDataController.GetData(String stores, DateTime date) in C:\Ang\Controllers\AcctLegendDataController.cs:line 330 at lambda_method(Closure , Object , Object[] ) at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters) at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync() at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync() at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync() at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter() at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context) at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync() at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync() at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext) at Microsoft.AspNetCore.Routing.EndpointRoutingMiddleware.Invoke(HttpContext httpContext) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)

1
Before you go too far down that road, read this.Gord Thompson

1 Answers

1
votes

So far I have -added Microsoft Access to the server. -added 32-bit and 64-bit Drivers in System Data Sources. -

Hold on! You added access, but what version? There is a x32 bit version, and a x64 bit version. So, unless you willing to force your web server to run as x32 bits, and run x32 IIS web server, then you very likley want to install the x64 bit version of access.

-added 32-bit and 64-bit Drivers in System Data Sources.

Not really possible. If you installed x64 bit access, then you not get a choice for accDB files in the x32 odbc config (windows) panel. You will ALWAYS get the ability to use a mdb file, since the JET data engine is installed on all copies of windows (and is x32 bits only version).

So, BEFORE you attempt to setup a connection, you need to ensure that the x64 bit version of access is installed. Once done, then you can use a oleDB provider or the ODBC provider. I VERY strong recommend you use the ODBC provider, since then you can with far greater ease flip the connection to say SQL server, or some other database, and your code changes will be the absolute min. (edit:and I see you ARE using ODBC here - so my bad, and your good!)

Once you add the x64 bit version of Access, then you should be able to connect to the database. And on your development machine, then you want from VS to tell IIS to use x64 bits, and while we are at this, you rather likly should get x64 bit access up and running on your dev box.