0

I have read a lot of information about this issue.
And there are many questions in SO for this , but the problem still remains and it is the following:

I have understand that this connection string is for the older versions of Office :

string oldCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
              + path + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'";

and I know that there is this connection string for the newer versions of Office:

string nweCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
              + path + ";Extended Properties=Excel 12.0;";

However , when I use the connection string for the newer versions with the ACE , I have this error :

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

I have also read in other questions that I should install ACE first and the everything will work fine.

  • I do not have ACE driver installed neither the other computers that should use this Application, is there a way to read the Excel files using the JET for Office 2016 or 365 (everything above 2003).

  • I can not install the ACE in every of my clients in order to be able to work with my Application , it is not practical at all.

Is there a way to do this?

LopDev
  • 823
  • 10
  • 26
  • You can probably look for keys called Microsoft.ACE.OLEDB.* under HKEY_CLASSES_ROOT in the registry to see what's installed. However if you can't install it yourself, and you can't rely on it being installed, you might need to use some other mechanism to read Excel files - there are libraries that will read them without relying on anything installed, but I don't think they have OleDB-like interfaces. – Rup Jul 09 '20 at 08:37
  • @Rup , could please help me with this? – LopDev Jul 09 '20 at 08:39
  • Sorry, I've never used these before myself. – Rup Jul 09 '20 at 08:40
  • Not really my field, but is your application compiled for the same bitness (32 v 64) as the version of **Office** on the target machine? – Rory Jul 09 '20 at 08:58
  • @Rory , yes but this is not the problem , the problem is that I want my application to run in other machines also , not only mine , this is the point that I stuck – LopDev Jul 09 '20 at 09:13
  • AFAIK, if your app is 64bit and the version of Office on the target machine is 32 bit, you're out of luck unless you can include a 64bit installer for the ACE provider in your setup.But, as I said, this is not really my area of expertise. – Rory Jul 09 '20 at 09:15
  • Does this answer your question? [Read/write Excel file without installing anything](https://stackoverflow.com/questions/66458071/read-write-excel-file-without-installing-anything) – Zev Spitz May 04 '21 at 11:36

1 Answers1

1

I had to solve the exact same problem many years ago. I added both connection strings to my configuration. When the application is used for the first time, I check with one ACE driver (since it is newer). If that fails, I will fallback to Jet. Save the working connectiion string in the AppConfig (you can save values to config too). So the next time you run the application, it will check the app config first and use that connection string on that machine.

Here are the steps I followed:

  1. When application is launched/used for the first time on the machine try to get the working connection string from AppConfig.
  2. Since it won't be available, you check with list of possible connection strings.
  3. Start with ACE driver. Is successful, save it in the AppConfig.
  4. If ACE failed, check with next driver (Jet). If this is successful, save this one as working connection string.
  5. Next time when you launch the application, check the working connection string and proceed with it. That way you are not checking for both drivers again and again.

Note: When you update setting to AppConfig file they are not directly updated to original file. A copy of file is created for that user under C:\Users<UserName>.... When you try to load the values from AppConfig, these two are "merged" and used. This is automatically handled in C#.

Based on the above, if you ever have to reset the selection, you have to remove the config file in the user folder above and that should start the process from step 1 again.

kvp2982
  • 151
  • 5
  • Thank you for your answer , but what does happen when the computer does not have the ACE driver and I want to read some .xlsx files , what should I do then ? – LopDev Jul 10 '20 at 06:38