在循环中打开第65个ADODB记录集时出现未指定的错误

Question

我有一些代码循环遍历文件夹中的一堆文件，并将数据加载到记录集中，将文件名和记录集存储在字典中（作为键/值对）供以后使用。该循环对于前64个文件工作良好，但是它在第65次尝试中给我一个“未指定错误”对话框。在recordData.Open行代码中断（循环调用下面的功能，所以对于每个循环中未显示）：

Public Function GetRecords(ByVal dataSrc as String) as ADODB.Recordset 

Dim dir as String 
Dim file as String 
Dim recordData as ADODB.Recordset 
Dim sql as String 
Dim conn as String 

dir = "C:\MyDirectory" 
file = dataSrc & ".csv" 

conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
       "Data Source=" & dir & ";" & _ 
       "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""" 

If DoesFileExist(dir & file) Then 
    sql = "select * from " & file & ";" 

    Set recordData = New ADODB.Recordset 

    recordData.CursorLocation = adUseClient 
    recordData.Open sql, conn, adOpenForwardOnly, adLockReadOnly, adCmdText 
Else 
    Set recordData = Nothing 
End If 

Set GetRecords = recordData 

recordData.Close 

End Function 

如果我重命名或删除文件，它目前停止，它的错误了上新的第65个文件，所以我知道实际的文件本身不是怪罪。我可以在一个会话中打开的记录集的数量是否有限制，还是我以低效的方式打开它们？

Answer 1

好了，经过修补后，我发现问题存在于连接中。我没有将连接字符串构建为字符串，而是创建了一个连接对象，然后在打开记录集后关闭它。必须对给定会话中可能存在的连接数量进行某种限制。这里是修改的功能：

Public Function GetRecords(ByVal dataSrc as String) as ADODB.Recordset 

Dim dir as String 
Dim file as String 
Dim recordData as ADODB.Recordset 
Dim sql as String 
Dim cn as ADODB.Connection 

dir = "C:\MyDirectory" 
file = dataSrc & ".csv" 

If DoesFileExist(dir & file) Then 
    sql = "select * from " & file & ";" 

    Set recordData = New ADODB.Recordset 
    Set cn = New ADODB.Connection 

    With cn 
     .Provider = "Microsoft.Jet.OLEDB.4.0" 
     .ConnectionString = "Data Source=" & dir & ";" & "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""" 
     .CursorLocation = adUseClient 
     .Open 
    End With 

    recordData.CursorLocation = adUseClient 
    recordData.Open sql, conn, adOpenForwardOnly, adLockReadOnly, adCmdText 

    cn.Close 
Else 
    Set recordData = Nothing 
End If 

Set GetRecords = recordData 

recordData.Close 

End Function