none
OleDbConnection.Open() throwing error RRS feed

  • Question

  • I am coding in VB.net 2012 and loading values from MS-Excel File using OLEBD connection but the Open() function is throwing error. The Code is as follows

    UnitsConnection = New OleDbConnection(sUnitsConnectionStr)   

    UnitsConnection.Open()


    The error looks likes this

    I have MDAC 2.5 installed but keeping this error in view I have downloaded MDAC 2.8 SP1 which I believe is the latest. The attempts to install MDAC 2.8 SP1 have failed without any error message. 

    Please help to resolve this.



    • Edited by RakLali Wednesday, November 15, 2017 8:20 AM
    Wednesday, November 15, 2017 6:25 AM

All replies

  • Anybody, please respond
    Thursday, November 16, 2017 2:49 AM
  • Hi RakLali,

    According to the following article,  in MDAC versions that are later than MDAC 2.6, Jet is not included with the MDAC components set; therefore, the Jet engine core components, the Jet ODBC driver, and the Jet OLE DB provider are no longer included as a part of MDAC.

    The latest Jet components are available as a part of MDAC 2.5 SP2. If you want to use MDAC 2.6 or later (including the latest Jet components), first install MDAC 2.5 SP2, and then upgrade to the latest Jet components. You can also obtain the latest Jet components for installation on existing MDAC 2.6 and later installations as a stand-alone distributable file. 

    https://msdn.microsoft.com/en-us/library/ms810805.aspx

    So the I suggest you just install Microsoft Access database engine.

    If your office is 2007, please install :https://www.microsoft.com/en-us/download/details.aspx?id=23734

    If your office is 2010 or later, please install:https://www.microsoft.com/en-us/download/details.aspx?id=13255

    Then you can use the following code to access ms excel:

      Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\output.xlsx;
    Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
            Dim con As New OleDbConnection(str)
            con.Open()
            Dim sql As String = "select * from [Sheet1$]"
            Dim cmd As New OleDbCommand(sql, con)
            Dim read As OleDbDataReader = cmd.ExecuteReader
            While read.Read()
                Console.WriteLine("The id is {0}, Fname is {1}, Lname is {2}", read(0), read(1), read(2))
            End While
            con.Close()

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 16, 2017 2:53 AM
    Moderator
  • Nope. Installing for Office 2010 did not help. Here is my Code

    Public sUnitsConnectionStr As String Public UnitsConnection As System.Data.OleDb.OleDbConnection Public UnitsDataAdapter As System.Data.OleDb.OleDbDataAdapter Public UnitsDtSet As System.Data.DataSet Public UnitsUpdateCommand As New System.Data.OleDb.OleDbCommand() sUnitsConnectionStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Me.Datapath & "\Databases\Units.xlsx; Extended Properties= 'Excel 12.0';" UnitsConnection = New OleDbConnection(sUnitsConnectionStr) UnitsConnection.Open()

    UnitsDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT Sector, Unit, Location from [Summary$]", UnitsConnection) UnitsDtSet = New System.Data.DataSet UnitsDataAdapter.Fill(UnitsDtSet, 3, MaxNumberOfRecordsinXlsx, "Summary")

    The error message is like this now

    UnitsConnection.Open()

    Please help




    • Edited by RakLali Thursday, November 16, 2017 7:46 AM
    Thursday, November 16, 2017 6:39 AM
  • What is your target CPU? Change it to x86 if it's not and try again.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, November 16, 2017 1:14 PM
  • it is x86


    • Edited by RakLali Thursday, November 16, 2017 1:19 PM
    Thursday, November 16, 2017 1:19 PM
  • I dont know what the deal is with this forum, but I cant seem to paste code. Looks like its been replaced with HTML ...

    Anyway, sometimes the quotes in the connection string will cause problems.

    IE: (note the quotation on extended properties.)

            Dim SourceXLSX As String = "C:\DATA\AllEmployees.xlsx"
            Dim ConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & SourceXLSX & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    -Edit. ok I found the code button, it just doesn't have a button image


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Thursday, November 16, 2017 1:32 PM
    Thursday, November 16, 2017 1:26 PM
  • Try the following working code sample which I did on Windows 7 64 bit setup the project as 32 bit.

    https://1drv.ms/u/s!AtGAgKKpqdWjiHUmPKU4GBCmyD96

    Here is a helper for connections.

    ''' <summary>
    ''' Setup for reading sheet data w/o header. If there is a header,
    ''' meaning first column is not data then set HDR=Yes
    ''' </summary>
    Module ConnectionHelper
        Public Function ConnectionString(ByVal FileName As String) As String
            Dim Builder As New OleDb.OleDbConnectionStringBuilder
            If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", "Excel 8.0;IMEX=2;HDR=No;")
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", "Excel 12.0;IMEX=2;HDR=No;")
            End If
    
            Builder.DataSource = FileName
    
            Return Builder.ConnectionString
        End Function
        Public Function ConnectionString(ByVal FileName As String, ByVal Header As String) As String
            Dim Builder As New OleDb.OleDbConnectionStringBuilder
            If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX=1;HDR={0};", Header))
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX=1;HDR={0};", Header))
            End If
    
            Builder.DataSource = FileName
    
            Return Builder.ConnectionString
        End Function
    End Module
    

    Read via a DataTable (could also use a data reader and loop too)

    Module Module1
    
        Sub Main()
            Dim SaveColor As System.ConsoleColor = Console.ForegroundColor
    
            Dim dt As New DataTable
            Dim FileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SampleData.xlsx")
    
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = ConnectionHelper.ConnectionString(FileName)
                }
    
                Console.WriteLine(cn.ConnectionString)
    
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .CommandText = "SELECT F1 As FirstName, F2 As MiddleName, F3 As LastName FROM [PeopleData$] ORDER BY F3",
                        .Connection = cn
                    }
    
                    Try
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                    Catch ex As Exception
                        Console.ForegroundColor = ConsoleColor.Red
                        Console.WriteLine("Error opening or loading data")
                        Console.WriteLine(ex.Message)
                        Console.ReadLine()
                        Exit Sub
                    End Try
    
                End Using
            End Using
    
            Console.ForegroundColor = ConsoleColor.Yellow
            Console.WriteLine("Rows will scroll by, once done use the console's scrollbar to review the data")
            Console.WriteLine("Press ENTER to continue")
            Console.ForegroundColor = SaveColor
    
            Console.ReadKey()
    
            For Each row As DataRow In dt.Rows
                Console.WriteLine(row.Field(Of String)("FirstName") & "," & row.Field(Of String)("LastName"))
            Next
            Console.ReadKey()
    
        End Sub
    
    End Module
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, November 16, 2017 1:45 PM
    Moderator
  • Thanks for all the efforts but still nothing is working. With Any CPU, the error message is ACE not registered. With x86, it is still asking for MDAC 2.6 or higher.

    I wish to add here that I had installed WAMPx6, MySQL Manager Console, Eclipse, etc etc for developing PHP/JSP projects also. Has this caused any such changes to Win 7 x64 environment that is causing me problems? The code was fine before all these changes.

    Friday, November 17, 2017 2:50 AM
  • Hi RakLali,

    If you have installed office 2010, your office version is 32 bit or 64 bit? did you install Microsoft Access database engine for the correct version?

    According to your error message, I guess that you don't install Microsoft Access database engine, please install it and try it again.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 17, 2017 3:11 AM
    Moderator
  • Sir/Madam, I did all that as per the guidance given in First Response. Yes ofcourse I did install for the correct version. But still no joy.
    • Edited by RakLali Friday, November 17, 2017 12:43 PM
    Friday, November 17, 2017 4:24 AM
  • Madam Karen, This is the error message I am getting on Running the Project you have provided

    Friday, November 17, 2017 4:59 PM
  • What version of windows? I'm running on windows 7, have not seen this before on a Windows 7 box.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, November 17, 2017 5:04 PM
    Moderator
  • I am on Windows 7 64, office Pro Plus 2010 32bit, VS 2012, .Net Framework 4.5 on Dell Inspiron 1564.

    I have MDAC 2.5 installed but remains unused on Adding in  Project References.

    I could only install MDAC 2.8 SDK but does not show when attempting to Add in References. Also, Windows is not allowing installation of MDAC 2.8 SP1. On running setup, it just shuts itself after self-extraction without throwing any error message.

    I do not have mdac.in in %windir%\inf  folder as is being suggested in some forums.

    Do I need to do some changes in Tools>Options...

     or Any Windows Environment?




    • Edited by RakLali Saturday, November 18, 2017 1:36 PM
    Friday, November 17, 2017 5:36 PM
  • Do I take it that there is no hope to resolve this problem?
    Saturday, November 18, 2017 4:26 PM
  • Hi,

    download Microsoft Access Database Engine, it may solve your problem !

    if you are using Access 2016 then

    https://www.microsoft.com/en-us/download/details.aspx?id=54920

    Saturday, November 18, 2017 4:30 PM
  • I did that sir, but in vain. I have tried all options that were suggested here and all that I could find on various forums on the internet. I ever uninstalled MySQL workbench and all associated softwares...

    I am using Office 2010 32 Bit. I have installed MS Access DB engine too. But still nothing.\

    I am on Windows 7 64, office Pro Plus 2010 32bit, VS 2012, .Net Framework 4.5 on Dell Inspiron 1564.

    I have MDAC 2.5 installed but remains unused on Adding in  Project References.

    I could only install MDAC 2.8 SDK but does not show when attempting to Add in References. Also, Windows is not allowing installation of MDAC 2.8 SP1. On running setup, it just shuts itself after self-extraction without throwing any error message.

    I do not have mdac.in in %windir%\inf  folder as is being suggested in some forums.

    Do I need to do some changes in Tools>Options...

     or Any Windows Environment?


    Do I need to Install SQL Server?

    • Edited by RakLali Saturday, November 18, 2017 4:53 PM
    Saturday, November 18, 2017 4:39 PM
  • What I do in your case is creating a new small windows forms project and do the minimum required to shadow the situation in the load event. 


    Success
    Cor

    Saturday, November 18, 2017 4:49 PM
  • I couldn't understand what you meant. Ms Karen Did give me a separate project to try but too threw the error image of which I have posted in above.

    Do I need to do some changes in Tools>Options...

     or Any Windows Environment?

    Do I need to Install SQL Server or make some changes there?

    Do I need to do something with IIS Manager? 
    • Edited by RakLali Saturday, November 18, 2017 5:21 PM
    Saturday, November 18, 2017 5:19 PM
  • I am on Windows 7 64, office Pro Plus 2010 32bit, VS 2012, .Net Framework 4.5 on Dell Inspiron 1564.

    I have MDAC 2.5 installed but remains unused on Adding in  Project References.

    I could only install MDAC 2.8 SDK but does not show when attempting to Add in References. Also, Windows is not allowing installation of MDAC 2.8 SP1. On running setup, it just shuts itself after self-extraction without throwing any error message.

    I do not have mdac.in in %windir%\inf  folder as is being suggested in some forums.

    Do I need to do some changes in Tools>Options...

     or Any Windows Environment?




    There is no need for MDAC, you need not worry about that. In regards to Windows 7 64, Office 2010, MDAC is part of Windows install. 

    I'm running Windows 7 64, have used Office 2003, 2007, 2010 and Office 365 and never had this issue (I do have the office driver installed and Access data engine).


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, November 18, 2017 5:44 PM
    Moderator
  • Ms Karen,  I have installed both Access data engine that is 2007 & 2010, but problem persists. 

    There seems to be some other problem of Windows Environment. I feel so because when I click Home Button on Chrome, it goes to my WAMP64 localhost directory. This may be one of the causes. I think I need to make IIS as default Localhost. I maybe wrong too. I am not sure. Another reason is, I had backed up my project files earlier. When  I load and run these backed up projects, same error is coming up and the Point of error is when I execution reaches following statement.

    UnitsConnection.Open()

    What all should I be seeing in VS 2012 Server Explorer? Only Local Computer name or other servers too? As of now I am seeing my local computer name and any Db connections I make


    • Edited by RakLali Saturday, November 18, 2017 6:24 PM
    Saturday, November 18, 2017 6:04 PM
  • Ms Karen,  I have installed both Access data engine that is 2007 & 2010, but problem persists. 

    There seems to be some other problem of Windows Environment. I feel so because when I click Home Button on Chrome, it goes to my WAMP64 localhost directory. This may be one of the causes. I think I need to make IIS as default Localhost. I maybe wrong too. I am not sure. Another reason is, I had backed up my project files earlier. When  I load and run these backed up projects, same error is coming up and the Point of error is when I execution reaches following statement.

    UnitsConnection.Open()

    What all should I be seeing in VS 2012 Server Explorer? Only Local Computer name or other servers too? As of now I am seeing my local computer name and any Db connections I make


    This is what I have.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, November 19, 2017 2:33 AM
    Moderator
  • Oh. Has this error messages got anything to do here

    Sunday, November 19, 2017 2:58 AM
  • I don't know, never seen this before.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, November 19, 2017 6:27 AM
    Moderator
  • Thanks M'am for all the help.
    Sunday, November 19, 2017 8:21 AM
  • Hi RakLali,

    Usually, if your office Pro Plus 2010 32bit, you just need to install Microsoft Access Database Engine 2010 Redistributable 32 bit, then it will works fine.

    https://www.microsoft.com/en-sg/download/details.aspx?id=13255

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 20, 2017 8:39 AM
    Moderator
  • No sir, its not happening. I even got VS 2017 but still to no avail. Same error, Get MDAC 2.6 or later.... :(
    Monday, November 20, 2017 4:01 PM
  • Hi,

    As far as I know Microsoft Data Access Components (MDAC) installed with Window OS. 

    According to : https://www.microsoft.com/en-us/download/details.aspx?id=5793 and https://msdn.microsoft.com/en-us/library/ms692877(v=vs.85).aspx

    The Microsoft Data Access Components (MDAC) 2.8 SP1 is Supported Operating System Windows 2000, Windows 98, Windows ME, Windows NT and is available for x86-based computers only, Since you are in X64 window7 so it would not work on.

    You can check Microsoft Data Access Components (MDAC) release history Here:

    https://support.microsoft.com/en-us/help/231943/microsoft-data-access-components-mdac-release-history

    People who have not encountered this problem would be hard to help you, since this problem is difficult to reproduce, I think searching for similar who may encounter the case is the best place

    Following threads may help you. following case may be helpful to you.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/8775ee9c-f48a-4f2e-89dd-92e5f5c59799/the-net-framework-data-providers-require-microsoft-data-access-componentsmdac?forum=netfxsetup

    http://www.dreamincode.net/forums/topic/303970-mdac-26-or-above-required-for-net-access/

    .

    It seems that Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine, 

    I recommend uninstall Office product and Microsoft Access Database Engine 2010 , and then install office 64bit, and Microsoft Access Database Engine 2010 x64.

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Sincerely,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 21, 2017 9:00 AM
  • Is it compulsory to have MS Office to make this project work or can I just uninstall everything as suggested by you to make it work?
    Tuesday, November 21, 2017 12:28 PM
  • Is it compulsory to have MS Office to make this project work or can I just uninstall everything as suggested by you to make it work?
    https://www.nuget.org/packages?q=Tags%3A%22Excel%22
    Tuesday, November 21, 2017 12:53 PM
  • Your MDAC install would appear to be corrupt. Since MDAC is a component installed with the OS, I would try running the System File Checker utility first.

    If this does not resolve the issue you will probably need to run either a Windows 7 repair or re-install Windows 7 altogether (backup your important files in either instance).


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, November 21, 2017 1:19 PM
  • Will installing MS Office 2016 64 help because I am not finding download link for Office 2010 64.
    Tuesday, November 21, 2017 7:09 PM
  • Will installing MS Office 2016 64 help because I am not finding download link for Office 2010 64.

    You can continue slamming into the wall or take the path of less resistance.
    Tuesday, November 21, 2017 11:12 PM
  • Will installing MS Office 2016 64 help because I am not finding download link for Office 2010 64.

    Microsoft continues to recommend using 32-bit Office and 32-bit Office components. Your VS project should be configured for x86 and the 32-bit ACE OLEDB Provider, which can be installed independently of Microsoft Office.

    Your MDAC issue is a separate problem.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, November 21, 2017 11:45 PM
  • Damn it. I uninstalled Office 2010...Now I lost my DVD too...
    Wednesday, November 22, 2017 1:26 AM
  • Can you please advise advise on the steps to "Conigure VS project for x86 and the 32-bit ACE OLEDB Provider". I am back with Office 2010 and also Office 2013. I have also also installed Microsoft Access Database Engine 2010 32bit. But still the error of MDAC 2.6 or later persists. 
    Thursday, November 23, 2017 2:24 AM
  • Can you please advise advise on the steps to "Conigure VS project for x86 and the 32-bit ACE OLEDB Provider". I am back with Office 2010 and also Office 2013. I have also also installed Microsoft Access Database Engine 2010 32bit. But still the error of MDAC 2.6 or later persists. 

    As I mentioned in my post on Tuesday, the MDAC problem is a separate issue. What is installed on your machine would appear to be corrupt. I suggested ways to fix it.

    To configure your project for 32-bit (x86) so that it works with 32-bit ACE OLEDB, select Build...Configuration Manager...and change the Platform option to x86.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, November 23, 2017 5:21 AM
  • Hi,

    Uninstall MDAC drivers and then re install MDAC 2.8 SP1,

    https://www.microsoft.com/en-us/download/details.aspx?id=5793

    see the installation guide:

    https://msdn.microsoft.com/en-us/library/ms810805.aspx?f=255&MSPPError=-2147217396

    if problem still persists then import your excel database to SQL server and try connecting with it !

    Friday, December 1, 2017 4:16 AM