Link to home
Start Free TrialLog in
Avatar of rkulp
rkulpFlag for United States of America

asked on

A Different "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

I have used the following code (with minor cosmetic differences) for about two years. This works fine on all previously compiled applications. While developing another application, it suddenly stopped working and gave me the error above. Interestingly enough, the debug folder had a previously compiled version that works but now I get the error. I don't know what caused it to suddenly appear. I even ran a previous app from the IDE and it worked fine. The new one gets the error. The code is essentially identical. I need some ideas on where to look to remedy this problem. I reinstalled the Access Database Engine, Office 2010 and Publisher 2013 and nothing helps.
  Private Sub btnBrowseClubFile_Click(sender As Object, e As EventArgs) Handles btnBrowseClubFile.Click
        Try
            With dlgOpenFile
                .Filter = "Excel Workbooks (*.XLS; *.XLSX)|*.XLS;*.XLSX|All Files (*.*)|*.*"
                .FilterIndex = 1
                If txtDistrictClubs.Text.Length > 0 Then
                    If (Trim(txtDistrictClubs.Text).ToUpper).EndsWith(".XLS") Or (Trim(txtDistrictClubs.Text).ToUpper).EndsWith(".XLSX") Then
                        If My.Computer.FileSystem.FileExists(Trim(txtDistrictClubs.Text)) Then
                            .FileName = Trim(txtDistrictClubs.Text)
                            .InitialDirectory = My.Computer.FileSystem.GetParentPath(Trim(txtDistrictClubs.Text))
                        ElseIf Not IsNothing(gDataPath) Then
                            .InitialDirectory = gDataPath

                        End If
                    End If
                ElseIf Not IsNothing(gDataPath) Then
                    .InitialDirectory = gDataPath
                End If
                If .ShowDialog = DialogResult.OK Then
                    txtDistrictClubs.Text = .FileName
                Else
                    btnBrowseClubFile.Focus()
                    Exit Sub
                End If
            End With
        Catch ex As Exception
            gMsg = "The following error occurred trying to define the Club Information Excel File: " & vbCrLf & ex.Message & vbCrLf & "at location " & gCurrentLocation & vbCrLf & "Do you want to try another file? Choose ""No"" to return to the main menu."
            gAns = MsgBox(gMsg, MsgBoxStyle.OkOnly + MsgBoxStyle.Information)
            Exit Sub
        End Try
        'Define the connection string and try to open the file.
        strClubsConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source = " & Trim(txtDistrictClubs.Text) & ";"
        If (Trim(txtDistrictClubs.Text).ToUpper.EndsWith(".XLS")) Then
            strClubsConnection += "Extended Properties=""Excel 8.0"";"
        Else
            strClubsConnection += "Extended Properties=""Excel 12.0 Xml"";"
        End If
        'Connection string defined. Now, make sure drClubsConn is nothing.
        If IsNothing(drClubsConn) Then
            drClubsConn = New OleDbConnection
        ElseIf drClubsConn.State = ConnectionState.Open Then
            drClubsConn.Close()
        End If

        With drClubsConn
            drClubsConn.ConnectionString = strClubsConnection
            'MsgBox("Connection String = " + vbCrLf + .ConnectionString)
            Try
                gCurrentLocation = "drClubsConn line 153ff"
                drClubsConn.Open()
            Catch ex As Exception
                gMsg = "Excel connection failed with the following error: " & vbCrLf & ex.Message & vbCrLf
                If Not IsNothing(ex.InnerException) Then
                    gMsg += "Inner Exception: " & vbCrLf & ex.InnerException.Message + vbCrLf
                End If
                gMsg += "Current Location = " + gCurrentLocation + ". "

                MsgBox(gMsg, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly)
                Exit Sub
            End Try
        End With
        gCurrentLocation = "Read District Clubs Line 162ff"
        Try
            SchemaTable = drClubsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
            gMsg = "Worksheets: " & vbCrLf
            For Each xlWorksheetName In SchemaTable.Rows
                If xlWorksheetName.Item(3) = "TABLE" Then
                    If InStr(xlWorksheetName.Item(2), "$") > 0 Then
                        cboClubsWorksheet.Items.Add(xlWorksheetName.Item(2))
                        gMsg += xlWorksheetName.Item(2) & vbCrLf
                    End If
                End If
            Next
            ' MsgBox(gMsg)
        Catch ex As Exception
            gMsg = "The following error occurred trying to read the Clubs Excel schema: " & vbCrLf & ex.Message & vbCrLf & "If this continues, contact the program author." & vbCrLf & "Do you want to try another file? Choose ""No"" to return to the main menu."
            gAns = MsgBox(gMsg, MsgBoxStyle.Critical + MsgBoxStyle.YesNo, "Database Schema Error")
            If gAns = MsgBoxResult.Yes Then
                btnOpenClubsWorksheet.Focus()
                Exit Sub
            Else
                gNormalUnload = True
                Me.Close()
                ' MainForm.Show()
            End If

        End Try
        cboClubsWorksheet.SelectedIndex = -1


    End Sub

Open in new window


The error occurs at the drClubsConn.Open() statement.
ASKER CERTIFIED SOLUTION
Avatar of Bembi
Bembi
Flag of Germany 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
Avatar of rkulp

ASKER

So far, the Microsoft suggested solution has always worked. Before I got your suggestion, I created a new project and essentially copied everything over. It worked fine. I sure wish I knew how the first project got messed up and identical code in the second one works. I will accept your answer and close the question. Thanks for your quick response.