读取Excel触发OleDbException：无法打开更多表，求代码排查

问题根源 #

你遇到的0x80004005错误，本质是循环中积累了大量未及时释放的OleDb资源，超出了ACE引擎同时打开表的2048上限。具体问题出在捕获程序扩展名的For循环里：

• 每次迭代都创建新的OleDbCommand和OleDbDataReader，但没有在循环内部关闭并释放这些对象，资源持续被占用。
• ReaderExcelProgramsColumn.HasRows的判断位置错误，应该在调用Read()之前检查，而非循环体内。

修复方案 #

方案1：循环内及时释放资源

在For循环的每次迭代末尾，立即关闭DataReader并释放Command，确保每一次查询的资源都被及时回收：

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

    ' 先检查是否有数据，再读取
    If ReaderExcelProgramsColumn.HasRows Then
        While ReaderExcelProgramsColumn.Read()
            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))
        End While
    Else
        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)
        ' 出错时也要释放资源
        ReaderExcelProgramsColumn.Close()
        sqlSelectColumnsExcelPrograms.Dispose()
        Exit Sub
    End If

    ' 每次循环结束后立即释放资源
    ReaderExcelProgramsColumn.Close()
    sqlSelectColumnsExcelPrograms.Dispose()
Next

方案2：一次性读取所有数据（更优）

避免循环查询Excel，改为一次性读取程序ID和扩展名两列的数据，在内存中匹配处理，彻底解决资源占用问题：

' 替换原来的两次查询逻辑，一次性读取需要的列
Dim strFullQuery As String = "SELECT [" & cboxExcelCIMCODBFieldProgram.Text & "], [" & strProgramFileExtensionNoPunctuation & "] FROM [" & cboxExcelTabName.Text & "$]"
Dim sqlSelectFullData As New OleDbCommand(strFullQuery, sqlExcelConnection)
Dim fullReader As OleDbDataReader = sqlSelectFullData.ExecuteReader

' 先把所有数据存入字典，方便后续快速查找
Dim programExtensionDict As New Dictionary(Of String, String)()
While fullReader.Read()
    Dim progId As String = Trim(UCase(fullReader.GetValue(0).ToString))
    Dim extension As String = Trim(UCase(fullReader.GetValue(1).ToString))
    If Not String.IsNullOrEmpty(progId) Then
        programExtensionDict(progId) = extension
    End If
End While

' 关闭并释放一次性查询的资源
fullReader.Close()
sqlSelectFullData.Dispose()

' 后续直接从字典中获取扩展名，无需再次查询Excel
For Each progId In arrProgramsInTheSpreadSheet
    lblStatus.Text = "Extracting the extension of the program '" & progId & "' from the spreadsheet..."
    Application.DoEvents()
    
    If programExtensionDict.ContainsKey(progId) Then
        tupleProgramsAndExtensions.Add((progId, programExtensionDict(progId)))
    Else
        MessageBox.Show(Me, $"未找到程序 {progId} 的扩展名信息", "数据缺失", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Exit Sub
    End If
Next

方案3：使用Using语句自动管理资源

使用VB.NET的Using语句，它会自动在代码块结束时调用对象的Dispose方法，避免手动释放遗漏：

' 示例：使用Using管理Connection、Command、Reader
Using sqlExcelConnection As New OleDbConnection(strConnectionStringExcel)
    sqlExcelConnection.Open()
    Using sqlSelectColumnsExcelPrograms As New OleDbCommand(strProgramsQuery, sqlExcelConnection)
        Using ReaderExcelProgramsColumn As OleDbDataReader = sqlSelectColumnsExcelPrograms.ExecuteReader
            ' 读取数据逻辑
        End Using
    End Using
End Using

额外注意事项 #

• 避免重复打开/关闭连接，尽量复用同一个连接完成所有操作。
• 你的代码中存在重复的ReaderExcelProgramsColumn.Close()和sqlSelectColumnsExcelPrograms.Dispose()调用，属于冗余操作，只需调用一次即可。

内容的提问来源于stack exchange，提问作者Daniel Santos