Link to home
Start Free TrialLog in
Avatar of zachvaldez
zachvaldezFlag for United States of America

asked on

Getting error in connectionstring with Excel.

Here's my code

  OleDbConnection excelconnection = new OleDbConnection();
            string excelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\ExportExcel\\ExportExcel.xlsx;Extended Properties = Excel 12.0 xml;HDR=YES;";

                   excelconnection.ConnectionString = excelconnectionstring;

                    excelconnection.Open(); ---- Here I get the error "the microsoft ace oledb 12 not registered in local machine".

I'm using Visual Studio 2015 and also for some reason I have to place "\\" on defining the path. If single "\", it errors  to unrecognized format..
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Does the system you are developing on have the Microsoft.ACE.OLEDB.12.0 drivers loaded? If not you will need to download and install on your system. The Microsoft Access Database Engine 2010 Redistributable can be downloaded from the [HERE].
Avatar of zachvaldez

ASKER

Before I download it, FYI, I'm in Windows 10 ,Excel 2013
It should be OK seeming it is the latest version of Microsoft Access Database Engine.
BTW how about  my question on the  unrecognized character "\" in the file path . I have to double it with "\\" to avoid the red curly line...
Yes, in C# in a string the \ character represents the start of a escape sequence. For example \t is a tab, \n is a new line. So in order to tell the compiler to use \ as a character and not the start of an escape sequence we double up on it and use \\. If you do not want to use escape  sequence in the string then you start the string with @, for example @"c:\folder1\folder2\..." will work with single \.
Thanks for clarifying. I have downloaded the AccessEngine for 32 bit, Now what the next steps?
From the link I posted previously do the install please note the two places that are bold and underlined.
Note: Before installing this download, you must first remove any previous versions of the Access Database Engine from your computer by using Add/Remove Programs in the Control Panel.

To install this download:
Download the file by clicking the Download button and saving the file to your hard disk.
Double-click the AccessDatabaseEngine.exe program file on your hard disk to start the setup program.
Follow the instructions on the screen to complete the installation.
To use this download:
If you are the user of an application, consult your application documentation for details on how to use the appropriate driver.
If you are an application developer using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”

If you are connecting to Microsoft Office Excel data, add the appropriate Extended Properties of the OLEDB connection string based on the Excel file type:

File Type (extension)                                             Extended Properties
---------------------------------------------------------------------------------------------
Excel 97-2003 Workbook (.xls)                                  "Excel 8.0"
Excel 2007-2010 Workbook (.xlsx)                             "Excel 12.0 Xml"
Excel 2007-2010 Macro-enabled workbook (.xlsm)      "Excel 12.0 Macro"
Excel 2007-2010 Non-XML binary workbook (.xlsb)      "Excel 12.0"

If you are an application developer using ODBC to connect to Microsoft Office Access data, set the Connection String to “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”
If you are an application developer using ODBC to connect to Microsoft Office Excel data, set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”
To remove this download:
To remove the download file itself, delete the file AccessDatabaseEngine.exe
On the Start menu, point to Settings and then click Control Panel.
Double-click Add/Remove Programs.
In the list of currently installed programs, select “Microsoft Access database engine 2010” and then click Remove or Add/Remove. If a dialog box appears, follow the instructions to remove the program.
Click Yes or OK to confirm that you want to remove the program.
I still get the error

 //Excel connection
           
             OleDbConnection excelconnection = new OleDbConnection();
            string excelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\ExportExcel\\ExportExcel.xlsx;Extended Properties=Excel 12.0 Xml;HDR=YES;";
            excelconnection.ConnectionString = excelconnectionstring;
            excelconnection.Open();>>>Here I still get the error "the microsoft ace oledb 12 not registered in local machine".
I downloaded the Access Database Engine and had the same issue. I looked at some documentation and found this issue with this all over the place. To remove the issue you will also need to download and install the 2007 Office System Driver: Data Connectivity Components please pay no mind to the 2007. Once I install this Connectivity Components it worked fine. You will also need to change the connection string it needs a couple of \" in it at the end as shown below.
string excelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\ExportExcel\\ExportExcel.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

Open in new window

I'm still getting the Provider error.
string excelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\ExportExcel\\ExportExcel.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
            OleDbConnection ExcelConnection = new OleDbConnection(excelconnectionstring);

            ExcelConnection.ConnectionString = excelconnectionstring;

            ExcelConnection.Open()>> I could not pass this
All I did with the link sent was to download it and run. Should I add anything in the Reference?
What version of Excel do you have, 32 bit or 64 bit?
When you say run you do mean installed it, correct?
I know I have o365 but I have local installed 2013
It's 32 bit
Have you tried closing down Visual Studio and restarting it?
I rebooted and still error appears.
I checked further. My software(Excel 2013) is 32 bit. My PC is 64 bit. I copied the code and run it on windows 7 and 32 bit PC , set platform of the app to x86 and I was able to pass the Provider error. BTW, I had to install the connectivity engine too. Just FYI
Then all is working not with loading the two packages I posted?
I loaded the one for 32 bit.
But this is for my win 7  PC . Not working with win 10
On the Window 10 machine is the Excel a 32 bit or 64 bit application?
32 bit
Whats the syntax for inserting the values in the column, I'm having errors formulating it

variables are unitID,headID,cn all strings. The idea is...

string insertSQl = "Insert into [Sheet1$](UnitID,HeadID,CN) values (unitID,headID,cn)"
Thanks many
That is a different issue please open a new question for that one.

On the Windows 10 machine you have the application setup the same was as Windows 7 like you stated below?
I checked further. My software(Excel 2013) is 32 bit. My PC is 64 bit. I copied the code and run it on windows 7 and 32 bit PC , set platform of the app to x86 and I was able to pass the Provider error. BTW, I had to install the connectivity engine too. Just FYI
which connectivity engine for 64bit? Can you send me link please. I know there were 2 on the list GThanjks many
IN my win10 machine, the platform for has to be x64 other wise it would not even execute. In win7 ,I have to switch platform to x86 and it runs there
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks
Not a problem zachvaldez, glad to help.