Question/Enhancement : Add support for selecting Access Provider version

[SheepReaper]

Currently you can only use Access Database Engine 12.0 (2007 era) I cannot get update-database to work with any other Engine. I'd like to use 16.0 (2016 era) - https://www.microsoft.com/en-us/download/details.aspx?id=54920

Even if I set my DBContext.OnConfiguring() to use Provider=Microsoft.ACE.OLEDB.16.0 update-databse -Verbose shows me that it's still trying to use 12.0 even if I don't have it installed. And even if 12.0 references the real 16.0 when installed, update-database creates the db file if not present, but then fails to run any queries against the db and just leaves it empty

[bubibubi]

About using the right provider, the right property should be JetConfiguration.OleDbDefaultProvider (its static). All the tests runs with 12 because they sets the connection directly (another way to use the provider you could try). You can also use App.Config to set the connection.

About DB creation and few other DDL statements the provider uses ADOX (dynamically referenced so if it's not present the provider raise an error but compiles) because Jet OLEDB provider does not implement different DDL statements (for example create DB and rename of objects). I don't know if your ADOX is the latest version (as well as I don't know if Microsoft updated it, it's ages that I don't use ADOX). Could your update-database issue related to this? About update-database if you can't solve it, if you send me the steps and the model I can try to reproduce it.

Thanks for the suggestions and for using the provider

[bubibubi]

Thanks for the commit!!! (I don't know why I'm not receiving emails when someone pushes a commit so it took a lot of time to see this...)

[thomasbevan]

@bubibubi could we get a nuget package with this fix in?

[bubibubi]

Just made a new release

[alan-hardwick]

Hi, I am trying to create a 2000 file format Access database to replicate our current version using EFC. I have tried using OLEDB.4 with OleDbDefaultProvider = "Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=4" without much success. Any suggestions? Thanks

[bubibubi]

You have to compile the app using 32bit configuration (Jet.OLEDB.4.0 is 32bit dll). What is the error you receive?

[alan-hardwick]

Using optionsBuilder.UseJet("Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=4;Data Source=MY.MDB"); with your recompiled .jet source of public static string OleDbDefaultProvider = "Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=4"; (everything compiled with x86), it creates a Access 2002 format database, but I need 2000 file format. Thanks

[SheepReaper]

From the engine’s perspective, Access 2000,2002,2003 format files all use the same engine. That is to say, Jet 4.0 will interact in the same way with any of those file formats.

The difference between Access 2000 and 2002-2003 formats come in with what the Gui Access Client can do. If you use any 2002/2003 features in that db file, then attempting to open the file with a 2000 client will cause issues. Hence why there is an arbitrary version difference in the 2 access file formats. But in reality the file formats are structurally the same.

If you are only accessing these files programmatically, using Jet, you should have no problems.

If you open these files with a Gui client, you will be restricted by the file format version. (You can always open the files with a newer-capable client, but you cannot open a 2002-2003 file with a 2000 Client)

Edit: Removed email extra

Jet 4.0 is backwards compatible with previous versions of Jet. The default value of the Engine Type parameter is 5. If specified, The provider is still Jet 4.0, but it emulates previous version behavior by limiting the SQL capabilities and adjusting file format version (when creating a file). The Engine Type parameter affects the following:

Engine Type 5 = Jet 4.X (4.0 SP8), Access 2002-2003 default file format Engine Type 4 = Jet 3.X (3.5? SP?), Access 97 default file format Engine Type 3 = Jet 2.X (2.5? SP?), Access 2.0 default file format Engine Type 2 = Jet 1.1 (SP?), Access 1.1 default file format Engine Type 1 = Jet 1.0 (SP?), Access 1.0 default file format

[alan-hardwick]

If I use my version of MSAccess GUI (Office 2016) I can open one of our current 2000 format files. I cannot find how to use the EF Core to create the same 2000 format .MDB file. Using OLEDB.4, depending on the Engine Type I use, my GUI MSAccess says it cannot open it as it is a previous version (perhaps '97 although I do not know how to confirm this), or it opens as a 2002 format. At present we wish to maintain the 2000 format, soI am investigating using EF Core with this file type. Thanks

[SheepReaper]

Engine Type 5 = Jet 4.0

using anything less results in a pre-2000 file.

Your problem stems from the fact that in Jet 4.0, when you create a new file, It will by default be the "current" version that that the engine supports.

I do not know of a way to create an mdb file in "2000" version mode. 2000 is essentially a "compatibility mode" version for 2002-2003.

2000 and 2002-2003 are structurally equivalent. You should only see a problem when using an Access 2000 Client.

[SheepReaper]

Maybe try to install an old version of Jet OleDb Provider 4.0 (Older than SP1)

in 4.0 pre-SP1 2002-2003 didn't exist yet. Maybe that's your answer.... Find an Office 2000 disk without the service pack.

Edit: I'm not sure what SP version 2002-2003 was introduced. The only version I can find that M$ lets you download is SP8 which does include 2002-2003, and another source indicates that 2000 shipped with SP1. But that may be way too difficult for what you need. I like Bubi's suggestion below to re-use an empty mdb already in 2000 version mode. <- this has the added advantage of not needing to downgrade your Jet Version.

[bubibubi]

You can also try to use an empty mdb that you already have. In the migration process you can create only the tables using the already existing mdb.

[alan-hardwick]

Excellent - thanks for all your help.