rkulp
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.
The error occurs at the drClubsConn.Open() statement.
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
The error occurs at the drClubsConn.Open() statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER