MS Access support in .net core

[MicaelMor]

I am wondering if there is currently any obstacle to having MS Access databases to work in .net core, from what I was able to find out part of the issue initially was that there was no System.Data.OleDb for .net core, but Microsoft has released this a few months ago https://www.nuget.org/packages/System.Data.OleDb so I am wondering if that was all that was missing or if there are other things missing still from .net core to make it work?

Thank you.

[Thorium]

I don't know, let's try. :-)

[Thorium]

That is now included in SQLProvider 1.1.79
It did compile easily.

But no-one has tried to create a project with it yet. :-)

[MicaelMor]

Hi,

Thanks for the update, I tried it with version 1.1.79 and 1.1.81 (retrieved using nuget), and I am getting the following error:

The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Unsupported database provider: MSACCESS

This is the same error as with older versions, which seems to come from SqlRuntime.DataContext.fs when trying to pattern match to see if it isn't using .net standard.
I have tried it with .net core and then tested it also with .net standard and it gives this error on both.

This is the code that I am running:

` open System
open System.Data
open FSharp.Data.Sql
open FSharp.Core

[<Literal>]
let useOptTypes = true
let connexStringName = "DefaultConnectionString"

let dbVendor = Common.DatabaseProviderTypes.MSACCESS

[<Literal>]
let resolutionPath = __SOURCE_DIRECTORY__

[<Literal>]
let ordenadosDBConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Micael\Desktop\Programas_be.accdb;User Id=;Password=;Jet OLEDB:System Database=;Jet OLEDB:Database password="

type mdb = SqlDataProvider<Common.DatabaseProviderTypes.MSACCESS, ordenadosDBConnectionString, ResolutionPath=resolutionPath, UseOptionTypes=useOptTypes >
let ctx = mdb.GetDataContext()`

which works in .net framework but not with .net core or .net standard

[Thorium]

Sorry, forgot one thing, can you try 1.1.82 please?

[MicaelMor]

Hi,

Thanks for the update, I am now getting the following error

The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Could not load file or assembly 'System.Data.OleDb, Version=4.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

This error only shows after I close and reopen the project after upgrading, if I just upgrade and don't close and reopen it will still show the old error.

I have tried also downloading System.Data.OleDb version 4.7 from nuget to see if it solved the error, but the error remains.

The .net framework project still works.

[Thorium]

As a temporary fix, can you try to copy the System.Data.OleDb.dll to nuget-cache
(e.g. C:\Users\(useraccount)\.nuget\packages\sqlprovider\1.1.82\lib\netstandard2.0\)
and tell if that helps?

(Edit: fixed the version in the path to 1.1.82)

[MicaelMor]

Hi,

Thanks for the tip, hadn't thought about doing that, I have copied the "System.Data.OleDb.dll" to the specified folder (1.1.82 not the 1.1.79 originally mentioned), and I am now getting the following error:

The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: System.Data.OleDb is not supported on this platform.

Have tried downgrading from .netcore 3.1 to 2.2, 2.0 and setting the target platform to x86 and x64, but none of them seemed to have made a difference.

Was able to get it going by using the oledb.ddl from the net461 folder instead of the .netstandard2.0 but as expected one then runs into problems with using a net framework package in a .net core application (in this case problems while writing to the DB).

[Thorium]

No it should be net461 version, because TypeProviders are compiled with .NET Framework and then in the runtime you should replace it with the .NET Core/Standard dll. This is how type providers work right now, which is messy, I hope they'll change it in the future.

Edit: So in your project you'll still reference the .NET Standard version of dll, just the .nuget package cache is the .NET Framework version. As I expect that did help, I'll fix this to be automatic in the next releases. :-)

[MicaelMor]

Hi,

In that case, after copying the file named System.Data.OleDb.dll from the "system.data.oledb.4.7.0.nupkg\lib\net461" into the specified 1.1.82 folder, the error that I am getting now is the following:

TypeLoadException: Could not load type 'System.Data.OleDb.OleDbConnection' from assembly 'System.Data.OleDb, Version=4.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.

This error only shows up after running the app, it does not show before compiling, unlike previous errors, more precisely the error seems to be triggered when trying to get the data context by using GetDataContext(), which is strange because the datacontext works fine in the IDE, so for example I can do something like:

type mdb = SqlDataProvider<Common.DatabaseProviderTypes.MSACCESS, wagesDBConnectionString, ResolutionPath=resolutionPath, UseOptionTypes=useOptTypes >
   
 let ctx = mdb.GetDataContext()
 let mdbctx = ctx.wagesDb.Employees.Individuals.``As Name``.``99, John Gold``

And the IDE will give me intelisense completion for records in the database and everything, proving that accessing the database is working, but as soon as I actually run this code (which works fine in .net Framework) it gives the previously mentioned error.

[Thorium]

We are very close here... Now, try the 1.1.83 package, don't touch the .nuget cache,
but reference System.Data.OleDb in your project and have a post-build or pre-build task to copy the dll to your bin-folder.

Something like this, depending on your OS, paths and TargetFramework:

  ...
  <ItemGroup>
    <PackageReference Include="System.Data.OleDb" Version="4.7.0" />
    <PackageReference Include="SQLProvider" Version="1.1.83" />
  </ItemGroup>
  <Target Name="AccessCopyLibrariesWin">
    <Exec Command="xcopy ..\packages\System.Data.OleDb\runtimes\win\lib\netstandard2.0\System.Data.OleDb.dll  bin\Debug\netcoreapp2.0\ /y" />
  </Target>
  <Target Name="PostBuild" AfterTargets="PostBuildEvent">
    <CallTarget Targets="AccessCopyLibrariesWin" />
  </Target>

I wonder what are the differences, and what should be used when in the package System.Data.OleDb:

\lib\netstandard2.0\System.Data.OleDb.dll (113KB)
\ref\netstandard2.0\System.Data.OleDb.dll (34KB)
\runtimes\win\lib\netstandard2.0\System.Data.OleDb.dll (348KB)

[MicaelMor]

Hi,

Thanks for the update, tried it along with the instructions you gave, and I am getting some errors when doing SubmitUpdates() on the dataContext.

When using a database I am using for another project I get this error:

System.AccessViolationException: 'Attempted to read or write protected memory. This is often an indication that other memory is corrupt.'

When using a blank database I created with just 1 test table and 1 test field I get the following error:

OleDbException: No error message available, result code: E_NOINTERFACE(0x80004002).

This exception was originally thrown at this call stack:
System.Data.OleDb.OleDbTransaction.ProcessResults(System.Data.OleDb.OleDbHResult)
System.Data.OleDb.OleDbTransaction.RollbackInternal(bool)
System.Data.OleDb.OleDbTransaction.Rollback()
FSharp.Data.Sql.Providers.MSAccessProvider.FSharp.Data.Sql.Common.ISqlProvider.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates(System.Data.IDbConnection, System.Collections.Concurrent.ConcurrentDictionary<FSharp.Data.Sql.Common.SqlEntity, System.DateTime>, FSharp.Data.Sql.Transactions.TransactionOptions, Microsoft.FSharp.Core.FSharpOption)
<StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.action@1-11(FSharp.Data.Sql.Runtime.SqlDataContext, System.Data.IDbConnection, Microsoft.FSharp.Core.Unit)
FSharp.Data.Sql.Runtime.SqlDataContext.FSharp.Data.Sql.Common.ISqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges()
<StartupCode$ConsoleApp6>.$Program.$Program() in Program.fs

Both these errors happen when calling SubmitUpdates(), just like before the code to do this in the databases works in a netframework project.

I also tried using the 3 different System.Data.OleDb.dll you posted in the xcopy command and none made any difference to the errors displayed.

[Thorium]

Ok, so it's basically working....?

Access drivers has its tricks, for the SubmitUpdates try to disable transactions:

let ctx = 
    db.GetDataContext(
        { Timeout = TimeSpan.MaxValue; 
          IsolationLevel = Transactions.IsolationLevel.DontCreateTransaction
        }:FSharp.Data.Sql.Transactions.TransactionOptions)

[Thorium]

One thing I remember with MS Access was that it cannot handle multiple simultaneous connections to the same database file. It used to give "Unknown Error" if that happened, e.g. by something leaving the connection open.

The "Attempted to read or write protected memory." is not SQLProvider error, but somewhere below that. Is there a specific case when this happens, or always? Does restarting the Visual Studio help?

[MicaelMor]

Unfortunately it isn't a multiple connection error, or any occasional thing, I had already tried to close and reopen Visual Studio, along with creating a new project in a different machine and copying the Db file to it, and I get the same error (for both databases) in the 2 machines I tested it in, and it also happens with x86 and x64.

Both databases seem to run into problems only with the SubmitUpdates() even if they are different problems, because I can access the DB in the IDE, and I can execute a query in the databases and get results back without running into any issue.
It is also not a code problem because this code runs in a netframework project.

Any other suggestions I can try?

[MicaelMor]

Was able to finally do a bit more digging and it seems this might be an issue with the version 4.7 of System.Data.OleDb, since the error seems to be the same as #dotnet/runtime#981 now issue #dotnet/runtime#32509