How to configure the provider to use ADOX?

[xoniuqe]

I am currently running in an issue on Windows 10 x64 machines which have seemingly no DAO drivers on them.
If I try to create the database via the IMigrator I get the following exception:

COMException: Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154 Class not registered

(FYI my connection string: Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; Data Source={0};Mode=Share Deny None;Jet OLEDB:Database Password={1} and I use the .UseJetOleDb Method to create the context)

After a bit of playing around I found that if just force the creation of the database via
JetDatabaseCreator.CreateInstance(SchemaProviderType.Adox).CreateDatabase(String.Format(CultureInfo.InvariantCulture, StaticDatabaseSettings.cCConnectionString, pDatabasePath, StaticDatabaseSettings.cCDatabasePassword));

Everything works just fine.
Here my question: Is this the intendet way of forcing this? Is this bad? What are alternative solutions?

For completion the code context where the issue occurs:

var migrator = (IMigrator)context.GetInfrastructure().GetService(typeof(IMigrator));
var appliedMigrations = context.Database.GetAppliedMigrations().ToList();
//DO some non ef core sql stuff
if (!pDatabasePath.Exists)
{
    // If this line is removed the mentioned exception occurs, if the database does not already exist
    JetDatabaseCreator.CreateInstance(SchemaProviderType.Adox).CreateDatabase(String.Format(CultureInfo.InvariantCulture, StaticDatabaseSettings.cCConnectionString, pDatabasePath, StaticDatabaseSettings.cCDatabasePassword));
}
if (appliedMigrations.LastOrDefault() != StaticDatabaseSettings.cTargetMigration)
{
    migrator.Migrate(StaticDatabaseSettings.cTargetMigration);
}

[ChrisJollyAU]

Can I ask what bitness you are compiling your program for? Any CPU,x86 or x64?

It's just that Microsoft.Jet.OLEDB.4.0 is somewhat legacy and only comes in an x86 version. If you try it from a different bit program you do get the error of "Class not registered"

[xoniuqe]

I am using the Any CPU Platform Target, but I still got the same error when setting that to x86. If I understand the ecosystem around access correctly this may still run into problems if the access drivers are installed in x64 on the target machine.

It's just that Microsoft.Jet.OLEDB.4.0 is somewhat legacy and only comes in an x86 version.

This issue is known but I have to work with an old mdb-file. As far as I know I cannot just replace that with an Microsoft.Ace.OLEDB.12.0 and expect nothing to go wrong?

[bubibubi]

it's not possible to create a database using an OLEDB connection so (as far as I remember) the provider creates an ADOX object not dependent on the OLEDB provider you are using (i.e. it ignores Microsoft.Jet.OLEDB.4.0).
When the database is created you can connect to it using any of the OLEDB providers you have installed on your PC (16 or 32 bits according to your application).
Note also that is quite hard to install 16 and 32 bits provider of the same version.

[ChrisJollyAU]

Thanks @bubibubi for that info.

Also @xoniuqe just to confirm, if you already have a mdb file, you are able to connect to it using OLEDB? If not, go to Build -> Configuration Manger, and make sure that the target platform for your project is set to x86. It IS possible to have a project to have a different target platform to the main target platform that shows in the menu area (.e.g. I have a solution that is set to Any CPU, but the specific project that needs to access oledb is set to x86

Alternative solution - not sure if this would work for your scenario or not. Have a blank mdb file stored somewhere as a template. Then when you need it, create a copy where you want it. Then you should be able to open it as normal and do whatever modifications it needs

[lauxjpn]

@xoniuqe As already stated above, Microsoft.Jet.OLEDB.4.0 is an old Jet OLE DB provider that is 32 bit. So if your application does not run in a 32 bit process, it will not be able to load it. The default ADOX and DAO versions that ship with with Windows should also only work for 32 bit processes.

To ensure you are running in a 32 bit process, check the return from Environment.Is64BitProcess in your app. You want to make sure, that this is also the case when you run the dotnet ef commands (assuming you are using those for migrations handling). The dotnet command can be run as 32 bit by running the 32 bit version of it (e.g. you can run & 'C:\Program Files (x86)\dotnet\dotnet.exe' ef ... from PowerShell).

---

Here are two links for 64 bit versions:

• Microsoft Access Database Engine 2016 Redistributable
• Microsoft Access Database Engine 2010 Redistributable

The compatibility of the 2010 version goes way back.

In case you want to use OLE DB as the data access provider (you have the choice between OLE DB and ODBC), and you want to explicitly specify the connection string, use Microsoft.ACE.OLEDB.16.0 for the 2016 redistributable and Microsoft.ACE.OLEDB.12.0 for the 2010 redistributable.

If you don't specify the OLE DB provider string, we will use the newest provider available.
If you don't specify a connection string, but just the path to the database file, we will build the connection string for you.
If you don't specify what data access provider (OLE DB or ODBC) you want to use, and you don't provide a connection string that makes it clear, we will use ODBC.

This issue is known but I have to work with an old mdb-file. As far as I know I cannot just replace that with an Microsoft.Ace.OLEDB.12.0 and expect nothing to go wrong?

Do you mean that you have to use the MDB file format, or that you have to use a very old MDB file format?

If it is the former, than any of the posted links (including the 2016 redistributable) will work for your 64 bit scenario.

If it is the latter, what specific Jet versions do you need to support (or what are the exact requirements that you have)?

---

After a bit of playing around I found that if just force the creation of the database via JetDatabaseCreator.CreateInstance(SchemaProviderType.Adox).CreateDatabase(String.Format(CultureInfo.InvariantCulture, StaticDatabaseSettings.cCConnectionString, pDatabasePath, StaticDatabaseSettings.cCDatabasePassword));
Everything works just fine.
Here my question: Is this the intendet way of forcing this? Is this bad? What are alternative solutions?

To support database creation and migrations for Jet as best as possible, we have to use both: DAO and ADOX. The reason is, that Microsoft once implemented everything Access related into DAO, than created ADO/ADOX as a successor to DAO (while ODBC was phased out in favor of OLE DB) and implemented newer Jet features into it, and finally later decided, that its not worth it and made DAO the primary library for Jet again, but without implementing every feature that has been made available using ADO/ADOX in the meantime.

We have two implementation under the hood, one using DAO and one using ADOX. Both have been implemented as complete as possible, but some features are only fully available using one of the two implementations. We therefore have a bridge class, that chooses the right implementation for the task at hand.

When it comes to database creation, in 99% of cases, it should not matter which implementation you are using.

Is this bad? What are alternative solutions?

The officially supported database creation call would be JetConnection.CreateDatabase(), which has a schemaProviderType parameter. It also ensures the existence of a dual table, which your code above does not.