none
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered ... RRS feed

  • Question

  • First - If I am in the wrong forum - please let me know - I'll happily delete and move this to the correct location.

    I have read through many threads, beating my head,

    Please be patient, I am converting Access VBA Code to VB.Net using Visual Studio 2017, and although I have used VB6 in the past, I am a little overwhelmed in the .Net environment.

    In Access VBA I used:

        Dim db As Database
        Dim rs As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset(Items_TBL)
                  
        If rs.EOF = False Then
            rs.MoveLast
            rs.MoveFirst
            sngCurrentQty = rs!TTL_QTY_ONHAND
            sngNewAmount = sngAdjustment + sngCurrentQty
        End If

    In attempts to recreate the process using VB.NET, I have scrapped posts from forums and have settled on the following code.

    Dim ConnectionString As String
    Dim SQLString as String
    Dim rs As OleDbDataReader
    
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                       "Data Source=C:\Users\darcy\OneDrive\Documents\Visual Studio 2017\Projects\cns\slackdata_2002.mdb;"
    
    Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        AccessConnection.Open()
    
        SQLString = "SELECT * FROM items_tbl"
    
    Dim AccessCommand As New System.Data.OleDb.OleDbCommand(SQLString, AccessConnection)
    
        rs = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)
    
        While rs.Read()
            sngCurrentQty = rs!TTL_QTY_ONHAND
            sngNewAmount = sngAdjustment + sngCurrentQty
        End While

    I am getting the exception at:  AccessConnection.Open()     
    System.InvalidOperationException: 'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.'

    Particulars of my installation

    • Office Professional 2016
      Access Version 1812 (Build 11126.20188 microsoft store) 32 bit
    • Windows 10 Pro (Version 1803)
    • Visual Studio 2017
      Target CPU (x86)

    Using the Server Explorer Data Connection, I attempted to connect to my 2016 access database using:  Data Source Microsoft Access Database File (OLE DB).  Initially it failed: 'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered ...

    I tried:

    1. Installing AccessDatabaseEngine_X64 - Rebooted
      Could not get connection to work
    2. Installed the regular AccessDatabaseEngine, rebooted
      No Success

    3. Exported Access 2016 .accdb to Access 2007 .mdb
      Success - I could make the connection through Solution Explorer

    However, I can not figure out how to get the code to work.  Or even if it is the best way to access the data.

    Constructive criticism readily excepted.  Hostile retorts ignored.

    Thanks in advance for any and all assistance.





    • Edited by DarCee28 Wednesday, January 9, 2019 11:36 PM
    Wednesday, January 9, 2019 11:28 PM

Answers

  • Hi DarCee28,

    >>How do I get x86 as a valid option?

    Select Any CPU in Copy settings from option. more information, see:

    In addition, please uninstall 64bit AccessDatabaseEngine and reinstall 32bit AccessDatabaseEngine and check if it works for you.

    If the issue still exists, could you please share a simple reproducible sample via OneDrive.

    Best regards,

    Zhanglong


    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.

    • Marked as answer by DarCee28 Saturday, January 12, 2019 11:46 PM
    Friday, January 11, 2019 7:29 AM
    Moderator

All replies

  • Hi DarCee28,

    Please check if the provide is installed via the following powershell command.

     (New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION

    If the installed "AccessDatabaseEngine" still does not work, please change the Active Solution Platform from "Any CPU" to "x86".

    For more information, please refer to:

    https://social.msdn.microsoft.com/Forums/en-US/f927a0a5-7cf5-4f98-9bbd-59eb997ec3f5/how-to-fix-8220the-microsoftaceoledb120-provider-is-not-registered-on-the-local?forum=adodotnetdataproviders

    Best regards,

    Zhanglong


    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, January 10, 2019 1:20 AM
    Moderator
  • First - thanks for the quick response

    The source is there - confirmed with the powershell command.

    SOURCES_NAME               SOURCES_DESCRIPTION
    ------------               -------------------
    Microsoft.ACE.OLEDB.12.0   Microsoft Office 12.0 Access Database Engine OLE DB Provider

    --------------------

    It is there, so I tried to set it from any CPU however x86 isn't available, I can create one, but it bases the configuration on any cpu or empty.  I set it any cpu - it failed, I set it for empty, it failed.

    How do I get x86 as a valid option?


    • Edited by DarCee28 Thursday, January 10, 2019 10:16 AM
    Thursday, January 10, 2019 10:15 AM
  • Hi DarCee28,

    >>How do I get x86 as a valid option?

    Select Any CPU in Copy settings from option. more information, see:

    In addition, please uninstall 64bit AccessDatabaseEngine and reinstall 32bit AccessDatabaseEngine and check if it works for you.

    If the issue still exists, could you please share a simple reproducible sample via OneDrive.

    Best regards,

    Zhanglong


    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.

    • Marked as answer by DarCee28 Saturday, January 12, 2019 11:46 PM
    Friday, January 11, 2019 7:29 AM
    Moderator
  • THANK YOU Sooooo Much!!!

    Your suggestions worked, and we are off and running with our project.

    Saturday, January 12, 2019 11:47 PM