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

0

After publishing the application I lose OdbcConnection/Command functionality.

I am trying to run this code below. I have a file that I am deleting information from, then updating it with new info from my code.

        //Connect to access .mdbcd 

        var conn = new OdbcConnection(@"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\wwwroot\mo22\storesales.mdb;Uid=****;Pwd=*****;ExtendedAnsiSQL=1;");
        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();

I have found many articles on this and have followed everyone's advice but I still cannot get this to work!

The connection string that worked in development was: var conn = new OdbcConnection(@"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\wwwroot\mo22\storesales.mdb");

So far I have added Microsoft Access to the server. Played around with multiple connection string formats. Also I have added System DSN such as Microsoft Access Driver and SQL Server. Maybe I am adding these Drivers incorrectly.

Could someone please help me solve this issue. I don't understand why it would work in development and not production!

Thanks

Here is the Error

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)

.net
ms-access
odbc
driver
dsn
asked on Stack Overflow Sep 16, 2019 by JSkyS • edited Sep 17, 2019 by JSkyS

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0