Alternative to system.data.oledb

[Stef_2891]

My c# application will run on target machine we’re I’m not administrator and I cannot register provider Microsoft.ace.oledb.12.0
My application uses this provider to transfer data from excel to access database .accdb.
For this reason I cannot install Microsoft access engine 2016
Is there any alternative to this library?
Is is possible anyhow to include this DLL in application during compilation?
I’m using .NET CORE v8 and windows 10/11

 

[Skydiver]

If you use .NET Framework 4.8 and stick to 32-bit, and have 32 bit Office installed on the machine, then there is nothing for you to install. Everything is already on the machine ready for you to use.

Why does the data need to go from Excel into Access?

Why can't VBA be used to script the data transfer instead?

There are a lot of commercial and open source libraries for reading and writing Excel files. Some of them only handle Excel files post Office 2003. For before 2003, you'll be in a harder bind to find a library that doesn't require Office to also be installed on the machine. You could also download the OpenXML SDK and go up the learning curve on how to read and write Excel files post Office 2003. Be warned that the learning curve is pretty steep if you want to actually understand what you are doing. If you are a copy-and-paste programmer and don't care about understanding what you are copying, then the learning curve is very shallow because you are not doing any learning -- just parrotting.

As for reading and writing Access database, that maybe a much harder thing to do without using the Access database drivers. As part of Microsoft's consent decree with the Department of Justice, Microsoft was required to document the Access file format. It's possible there are some open source libraries which has taken that documentation and figured out how to make things work.

 

[Stef_2891]

If you use .NET Framework 4.8 and stick to 32-bit, and have 32 bit Office installed on the machine, then there is nothing for you to install. Everything is already on the machine ready for you to use.

Why does the data need to go from Excel into Access?

Why can't VBA be used to script the data transfer instead?

There are a lot of commercial and open source libraries for reading and writing Excel files. Some of them only handle Excel files post Office 2003. For before 2003, you'll be in a harder bind to find a library that doesn't require Office to also be installed on the machine. You could also download the OpenXML SDK and go up the learning curve on how to read and write Excel files post Office 2003. Be warned that the learning curve is pretty steep if you want to actually understand what you are doing. If you are a copy-and-paste programmer and don't care about understanding what you are copying, then the learning curve is very shallow because you are not doing any learning -- just parrotting.

As for reading and writing Access database, that maybe a much harder thing to do without using the Access database drivers. As part of Microsoft's consent decree with the Department of Justice, Microsoft was required to document the Access file format. It's possible there are some open source libraries which has taken that documentation and figured out how to make things work.

Unfortunately target machine are in 64 bit and cannot be installed .net framework, that’s why I published framework into application via compilation. I selected .NET 8 because think it gives full compatibility longer over time and no regressione with different windows version, do framework 4.8 give full compatibility?
I have no problem with reading and writing exce files because of I used open source libraries like officexml, that do no need interoperability with office package. I have the requirement my application shall be in c# and it needs to interface with already existing .accdb.
The only issue is oledb command when takes data from excel to access.

 

[Skydiver]

.NET Framework 4.8 comes pre-installed with modern versions of Windows.

If the Access database is already pre-existing on the target machine, presumably, Access is also installed on the machine, or at least the Access drivers are installed there otherwise, the file is useless.

So the only question will be if the 32-bit or 64-bit version of Office is installed on the machine. It sounds like the target machine is on some kind of corporate system. Most large corporations still use 32-bit Office rather than 64-bit Office despite having a 64-bit OS because it solves most of the headaches of legacy Office add-ins and VBA scripts that assume 32-bit.

 

[Skydiver]

Also, if this is a corporate sanctioned project, then that means you can work with the IT department to have them deploy your app. That will effectively let you install the driver's you need.

But first confirm the exact details of your target environment.

 

[Stef_2891]

.NET Framework 4.8 comes pre-installed with modern versions of Windows.

If the Access database is already pre-existing on the target machine, presumably, Access is also installed on the machine, or at least the Access drivers are installed there otherwise, the file is useless.

So the only question will be if the 32-bit or 64-bit version of Office is installed on the machine. It sounds like the target machine is on some kind of corporate system. Most large corporations still use 32-bit Office rather than 64-bit Office despite having a 64-bit OS because it solves most of the headaches of legacy Office add-ins and VBA scripts that assume 32-bit.

This is not true. Access is installed without any library from Microsoft access engine database. The same for .net, it is not installed and it is working on windows 11. This is company policy.

 

[Skydiver]

From Microsoft:

.NET Framework 4.8 is included with Windows 11, and runs any .NET Framework 4.x app.

Install .NET Framework on Windows 11 - .NET Framework

Learn how to install .NET Framework on Windows 11.

learn.microsoft.com

Perhaps your company made custom image that strips away .NET Framework 4.8?

 

[Skydiver]

Seems to work for me on Win11, 32-bit .NET Framework 4.8, no MDAC, but Access install as part of the Office suite:

Perhaps you might have a more locked down system than we do over here.

 

[Skydiver]

Anyway, on your target machine, check to see if there is a HKEY_CLASSES_ROOT\Microsoft.ACE.OLEDB.12.0. If there is, check the CLSID associated with that ProgID:

Search for the CLSID:

If there is an InprocServer32 entry for it, then that means there is an OLEDB handler for "Microsoft.ACE.OLEDB.12.0".

 

[Stef_2891]

Anyway, on your target machine, check to see if there is a HKEY_CLASSES_ROOT\Microsoft.ACE.OLEDB.12.0. If there is, check the CLSID associated with that ProgID:
View attachment 3054
Search for the CLSID:
View attachment 3055
If there is an InprocServer32 entry for it, then that means there is an OLEDB handler for "Microsoft.ACE.OLEDB.12.0".

I confirm on target machine it is installed windows 10 with any .NET framework installed from app section

 

[Stef_2891]

I confirm on target machine it is installed windows 10 with any .NET framework installed from app section

Anyway it seems CLSID is present and related oledb DLL but I don’t understand why it is requiring a registration on local machine

 

[Skydiver]

You probably compiled your app for 64-bit rather than 32-bit.

Notice the second screenshot I showed. It has the CLSID in the WOW6432 compatibility hive, not the regular hive.

 

[Stef_2891]

You probably compiled your app for 64-bit rather than 32-bit.

Notice the second screenshot I showed. It has the CLSID in the WOW6432 compatibility hive, not the regular hive.

I’ll publish 32 bit version and I’ll keep you informed

 

[Stef_2891]

I’ll publish 32 bit version and I’ll keep you informed

I’m doing some try with unsuccessful outcomes. However I understood the issue is non on the target machine because if I compiled my app with 32bit it doesn’t work and when connection is open, I’m debugging as well, an exception is generated, Microsoft.ace.oledb.12.0 is not registered on your local machine therefore IHave to compile with “any cpu” but if I go on target I got same issue.

 

[Skydiver]

"Any CPU" with "Prefer 32-bit" will want to run 32-bit on 64-bit Windows that has an Intel or AMD processor, if you are targeting .NET Framework 4.5 through 4.8.x

Did you check the registry on that target machine to see if the ACE OLEDB handler is installed?