I apologize in advance if this has been answered before. I searched for it but could not find anything explaining this particular issue.
I'm getting the OleDb exception 0x80004005 - Cannot open any more tables while reading a large Excel spreadsheet. It parses around 2000 records correctly than breaks.
I searched for this error, which is related to a limit of 2048 tables being open at a given time. I cannot see where my code is wrong, and I think it is, but I need a fresh pair of eyes looking at me to point me out what I'm missing.
This is the code - As you can see, I am closing the reader ReaderExcelProgramsColumn
What am I not seeing?
If strSpreadSheetFullPathLowCase.Contains(".xlsx") Then
strConnectionStringExtendedParameters = ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;Mode=Read;Readonly=True"";"
ElseIf strSpreadSheetFullPathLowCase.Contains(".xlsm") Then
strConnectionStringExtendedParameters = ";Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1;Mode=Read;Readonly=True"";"
End If
Dim intColumStartLocator As Integer = 0
Dim strConnectionStringExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFilePathOpenExcel.Text & strConnectionStringExtendedParameters
Dim strProgramID As String = Nothing
Dim strMachinePrefix As String = Nothing
Dim strProgramFileExtensionFromExcel As String = Nothing
Dim strLastFileName As String = Nothing
Dim strLastProgramID As String = Nothing
Dim strFullFilePath As String = Nothing
Dim sqlExcelConnection As OleDbConnection = Nothing
Try
'/////////////////////////////////////////////
'// Open the connection with the Excel File //
'/////////////////////////////////////////////
lblStatus.Text = "Opening connection with MS-Excel..."
Application.DoEvents()
sqlExcelConnection = New OleDbConnection(strConnectionStringExcel)
sqlExcelConnection.Open()
Catch ex As Exception
If (sqlExcelConnection.State = ConnectionState.Open) Then
sqlExcelConnection.Dispose()
End If
ErrorToReadFromMSExcel(ex.ToString, System.Reflection.MethodInfo.GetCurrentMethod.ToString, True)
Exit Sub
End Try
Try
Dim strProgramsQuery As String = "SELECT" & vbNewLine &
"[" & cboxExcelCIMCODBFieldProgram.Text & "]" & vbNewLine &
"FROM [" & cboxExcelTabName.Text & "$]"
Dim Query As String = String.Empty
Dim sqlSelectColumnsExcelPrograms As New OleDbCommand(strProgramsQuery, sqlExcelConnection)
Dim ReaderExcelProgramsColumn As OleDbDataReader = sqlSelectColumnsExcelPrograms.ExecuteReader
Do While ReaderExcelProgramsColumn.Read()
If ReaderExcelProgramsColumn.HasRows Then
strProgramID = Trim(UCase(ReaderExcelProgramsColumn.GetValue(0).ToString))
Dim strQuery As String = "SELECT" & vbNewLine &
"hdrfullprefix" & vbNewLine &
"FROM " & strCustomNameForHeaderSettingsTable & " AS slb" & vbNewLine &
"INNER JOIN " & strTableMchGroups & " AS mg" & vbNewLine &
"WHERE mg." & strMchGroupID & " = slb.machidncbase" & vbNewLine &
"AND mg." & strMchGroup & " = '" & cboxCIMCOMachineToMigrate.Text & "'" & vbNewLine &
"ORDER BY id DESC" & vbNewLine &
"LIMIT 1"
strMachinePrefix = RunBasicQueryReturnIsStringOnNCBase(strNCBaseConnectionString, strQuery)
If strProgramID <> String.Empty Then
lblStatus.Text = "Extracting program '" & strProgramID & "' from the spreadsheet..."
Application.DoEvents()
arrProgramsInTheSpreadSheet.Add(strProgramID)
arrProgramsPrefixes.Add(strMachinePrefix)
End If
If arrProgramsInTheSpreadSheet.Count = 0 Then
MessageBox.Show(Me, "Could not find programs in the spreadsheet in the column '" & cboxExcelCIMCODBFieldProgram.Text & "', tab '" & cboxExcelTabName.SelectedItem.ToString & "' of the selected spreadsheet!" _
& vbNewLine & vbNewLine & "Please check if the column name selected for program extraction is correct in the spreadsheet and try again.", "Could not find programs in the selected spreadsheet!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub
End If
Else
ReaderExcelProgramsColumn.Close()
MessageBox.Show(Me, "Could not find programs in the spreadsheet in the column '" & cboxExcelCIMCODBFieldProgram.Text & "', tab '" & cboxExcelTabName.SelectedItem.ToString & "' of the selected spreadsheet!" _
& vbNewLine & vbNewLine & "Please check if the column name selected for program extraction is correct in the spreadsheet and try again.", "Could not find programs in the selected spreadsheet!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub
End If
strLastProgramID = strProgramID
Application.DoEvents()
Loop
ReaderExcelProgramsColumn.Close()
sqlSelectColumnsExcelPrograms.Dispose()
'Close connection to release memory
ReaderExcelProgramsColumn.Close()
sqlSelectColumnsExcelPrograms.Dispose()
sqlExcelConnection.Dispose()
'Open a new connection
sqlExcelConnection = New OleDbConnection(strConnectionStringExcel)
sqlExcelConnection.Open()
lblStatus.Text = "Starting data extraction... Please wait..."
Application.DoEvents()
'*** Sort the array ***
arrProgramsInTheSpreadSheet.Sort()
'*** Capture Program Extensions ***
For i As Integer = 0 To arrProgramsInTheSpreadSheet.Count - 1
strProgramsQuery = "SELECT TOP 1 [" & strProgramFileExtensionNoPunctuation & "] FROM [" & cboxExcelTabName.Text & "$] WHERE [" & cboxExcelCIMCODBFieldProgram.Text & "] = '" & arrProgramsInTheSpreadSheet.Item(i) & "'"
sqlSelectColumnsExcelPrograms = New OleDbCommand(strProgramsQuery, sqlExcelConnection)
ReaderExcelProgramsColumn = sqlSelectColumnsExcelPrograms.ExecuteReader
Do While ReaderExcelProgramsColumn.Read()
'*****************************************************************************
'** The exception 0x80004005 - Cannot open any more tables occurs somewhere **
'** here, even after I closed the connection between the loops. **
'*****************************************************************************
If ReaderExcelProgramsColumn.HasRows Then
strLastProgramID = arrProgramsInTheSpreadSheet.Item(i)
lblStatus.Text = "Extracting the extension of the program '" & arrProgramsInTheSpreadSheet.Item(i) & "' from the spreadsheet..."
Application.DoEvents()
strProgramFileExtensionFromExcel = Trim(UCase(ReaderExcelProgramsColumn.GetValue(0).ToString))
tupleProgramsAndExtensions.Add((arrProgramsInTheSpreadSheet.Item(i), strProgramFileExtensionFromExcel))
Else
ReaderExcelProgramsColumn.Close()
MessageBox.Show(Me, "HeaderPuncher could not find programs in the spreadsheet in the column '" & cboxExcelCIMCODBFieldProgram.Text & "', tab '" & cboxExcelTabName.SelectedItem.ToString & "' of the selected spreadsheet!" _
& vbNewLine & vbNewLine & "Please check if the column name selected for program extraction is correct in the spreadsheet and try again.", "Could not find programs in the selected spreadsheet!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub
End If
Loop
Next
ReaderExcelProgramsColumn.Close()
sqlSelectColumnsExcelPrograms.Dispose()
sqlExcelConnection.Dispose()
Thanks!
