zachvaldez
asked on
Getting error in connectionstring with Excel.
Here's my code
OleDbConnection excelconnection = new OleDbConnection();
string excelconnectionstring = "Provider=Microsoft.ACE.OL EDB.12.0;D ata Source=c:\\ExportExcel\\Ex portExcel. xlsx;Exten ded Properties = Excel 12.0 xml;HDR=YES;";
excelconnection.Connection String = 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..
OleDbConnection excelconnection = new OleDbConnection();
string excelconnectionstring = "Provider=Microsoft.ACE.OL
excelconnection.Connection
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..
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].
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.
ASKER
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 \.
ASKER
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.
ASKER
I still get the error
//Excel connection
OleDbConnection excelconnection = new OleDbConnection();
string excelconnectionstring = "Provider=Microsoft.ACE.OL EDB.12.0;D ata Source=C:\\ExportExcel\\Ex portExcel. xlsx;Exten ded Properties=Excel 12.0 Xml;HDR=YES;";
excelconnection.Connection String = excelconnectionstring;
excelconnection.Open();>>> Here I still get the error "the microsoft ace oledb 12 not registered in local machine".
//Excel connection
OleDbConnection excelconnection = new OleDbConnection();
string excelconnectionstring = "Provider=Microsoft.ACE.OL
excelconnection.Connection
excelconnection.Open();>>>
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;\"";
ASKER
I'm still getting the Provider error.
string excelconnectionstring = "Provider=Microsoft.ACE.OL EDB.12.0;D ata Source=C:\\ExportExcel\\Ex portExcel. xlsx;Exten ded Properties=\"Excel 12.0 Xml;HDR=YES;\"";
OleDbConnection ExcelConnection = new OleDbConnection(excelconne ctionstrin g);
ExcelConnection.Connection String = excelconnectionstring;
ExcelConnection.Open()>> I could not pass this
string excelconnectionstring = "Provider=Microsoft.ACE.OL
OleDbConnection ExcelConnection = new OleDbConnection(excelconne
ExcelConnection.Connection
ExcelConnection.Open()>> I could not pass this
ASKER
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?
ASKER
I know I have o365 but I have local installed 2013
ASKER
It's 32 bit
Have you tried closing down Visual Studio and restarting it?
ASKER
I rebooted and still error appears.
ASKER
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?
ASKER
I loaded the one for 32 bit.
ASKER
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?
ASKER
32 bit
ASKER
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
variables are unitID,headID,cn all strings. The idea is...
string insertSQl = "Insert into [Sheet1$](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?
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
ASKER
which connectivity engine for 64bit? Can you send me link please. I know there were 2 on the list GThanjks many
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
Not a problem zachvaldez, glad to help.