CODEWITHSUNDEEP
c#excelconnection-stringoledbms-jet-ace
LopDev
LopDev

Reputation: 839

Problem with Excel file reading with C# , ACE problem

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.

Is there a way to do this?

Upvotes: 0

Views: 388

Answers (1)

kvp2982
kvp2982

Reputation: 151

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.

Upvotes: 1

Related Questions