Unspecified Error with MSAccess database

fsprojects / SQLProvider

A general F# SQL database erasing type provider, supporting LINQ queries, schema exploration, individuals, CRUD operations and much more besides.

https://fsprojects.github.io/SQLProvider

Other

581 stars 146 forks source link

Unspecified Error with MSAccess database #340

Closed isaacabraham closed 7 years ago

isaacabraham commented 8 years ago

Trying out with an MS Access DB - connection string looks something like this: -

type Database = SqlDataProvider<ConnectionString = @"Data Source=path\to\access-db.accdb;Provider=Microsoft.ACE.OLEDB.12.0", DatabaseVendor = DatabaseProviderTypes.MSACCESS>

This actually worked once I installed the (correct version of) Access Database Engine.

However, after an unspecified period of time - and only after I start exploring the database in a script - the declaration of the TP breaks with the error The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Unspecified error'. However, FSI continues to work - it's just in the script file that the error shows up.

I've tried both 32 and 64 bit versions of the engine, and both VSCode and VS2015 - same error always occurs.

I'm not in a position to share the database out unfortunately, but am happy to help diagnose this problem if possible.

Thorium commented 8 years ago

What version are you using? The latest?

Thorium commented 8 years ago

This is probably Access error of the database file being locked while there are multiple connections open.

isaacabraham commented 8 years ago

Latest version (1.0.35). I've made sure Access itself isn't open when working with the database - what should I do?

Thorium commented 8 years ago

The problem occurs when SQLProvider closes the connection and tries to reconnect. That's why there are some exceptions in source code compared to other providers that Access connections are left open.

isaacabraham commented 8 years ago

Don't understand :-( Wouldn't this mean that everyone using Access sees this error?

isaacabraham commented 8 years ago

I think you're right though - basically until I actually do a "Send to Interactive" it's fine. But once FSI gets involved as well, it goes red when I start doing a few commands.

A .laccdb file is also created after I open up the script that references the DB.

pezipink commented 8 years ago

pinging @bennylynch on this one since he wrote the access implementation and is still being used as far as I understand..

Thorium commented 8 years ago

Ok, the problem here is:

If we wouldn't use transactions and we have an operation like "1. select item, 2 update values, 3. save item", there is a theoretical possibility that someone else is modifying the item after step 1 and before step 3, causing the item maybe to be in a corrupted state.
We would like to use DTC-transactions for CRUD-operations. For DTC, the default scope is "attach to existing or create new if none found". We have option to use ADO-database transactions, yes, but they are not spreading over multiple operations like DTCs are.
If connection wants to join a newly created DTC-transaction then the connection has to be made inside a transaction, i.e. connection has to be closed and reopened to attach it to this transaction.
If connection is ever closed, Access starts to raise Unknown Errors.
In other words: we can select do we want DTC-transactions or do we want working intellisense.

Thorium commented 8 years ago

This should be working better now, @isaacabraham can you test do the problem still exists?