none
Microsoft Access Database Engine 2016 Redistributable used in VB.net 2017 could not open connection to excel file RRS feed

  • Question

  • I have a clean (just imaged by corporation IT) system with 64bit Win-10, 64 bit Office 365, and then Visual Studio 2017 Professional. Then installed Microsoft Access Database Engine 2016 Redistributable. As known, only the 64 bit can be installed.

    I want to use VB.net to read an Excel file (.xls) using the database jet engine method.

    I used VB to create a 'Visual Basic Window Classic, Window Form App (,NET 4.0)' application with only one button and by clicking it to run codes to open an xls file

    Additional references of the Application include

    Microsoft ActiveX Data Objects 6.1 Library
    Microsoft Excel 16.0 object library
    Microsoft Office 16.0 object library

    The codes are

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim xlsFName As String
            xlsFName = "C:\LP1-3-Jap02.D\Report01.xls"
            Dim oConn As ADODB.Connection
            oConn = New ADODB.Connection
            oConn.Open("Provider=Microsoft.ACE.OLEDB.16.0;" &
                       "Data Source=" & xlsFName & ";" &
                       "Extended Properties=""Excel 5.0;HDR=NO;""")

        End Sub
    End Class

    When it runs, it crashed at the line where the connection is to be opened.

    Error Message is <Provider cannot be found. It may not be properly installed>

    A similar set of code used to work with 'Microsoft.Jet.OLEDB.4.0' for a long long time until recently and was reported to be due to two Microsoft Window security updates.

    I have also tried to use the Microsoft Access Database Engine 2010 Redistributable, but it did the same thing (I did change the provider to 14.0 (in fact I tried all numbers from 10 to 16).

    I also tried to make a reference to the jet engine

    e.g. Microsoft office 14.0 access database engine object library... no success.

    Really appreciate for any suggestion!

    Thursday, November 16, 2017 8:11 PM

All replies

  • The following is how I would connect to Excel. It's a console project opening a .xlsx but if you look at the code module included it creates the connection string on the fly based on the file extension.

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


    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 8:40 PM
    Moderator
  • Hi chi-lo,

    If you have installed Microsoft Access Database Engine 2016, I guess that you still need to set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”  If you are connecting to Microsoft Office Excel data, you can get this detailed info from the following link--install instructions

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

    The connection string is:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
    Extended Properties="Excel 12.0 Xml;HDR=YES";

    The following code is the example that you can refer to:

    Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\output.xls;
    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.

    • Proposed as answer by Stanly Fan Monday, November 20, 2017 12:55 AM
    Friday, November 17, 2017 5:47 AM
    Moderator