ODBC对象连接存在问题-打开限制为65k行。
ODBC对象连接存在问题-打开限制为65k行。
我刚开始学习编程,包括SQL和VBA。我经常使用较大的数据表,并且认为为现有的Excel表格添加SQL查询执行能力会很有帮助。研究后,我发现ADODB连接可以实现这个功能,并在这里找到了一个很好的基础代码片段:https://blog.learningtree.com/excel-as-a-database-how-to-query-economic-data-with-sql/。但是我发现在访问行数超过一定限制后,下一行代码就会出错。在我的SQL语句中,我可以返回65000行,如果定义源表大小超过这个数量,就会出现“对象不存在”的错误。请问是否可以异步运行ADODB的recordset.Open
方法,以确保完全返回对象?任何帮助将非常感激。谢谢!我尝试在代码中插入了一个WAIT命令:rs.Open strSQL, Application.Wait (Now + TimeValue("0:00:30")), cn
,但仍然出错。请参考下面的代码:
Sub ExcelTbl_SQL() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset strFile = ThisWorkbook.FullName strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";" Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open strCon strSQL = "SELECT * FROM [Sheet1$A1:AI146103] WHERE GROUP = 'HIX'" rs.Open strSQL, cn Dim ws As Worksheet Set ws = Application.Sheets.Add ws.Range("A1").CopyFromRecordset rs rs.Close cn.Close 'Debug.Print rs.GetString End Sub
这是我得到的结果:
- 可行:
strSQL = "SELECT * FROM [Sheet1$A1:AI65000] WHERE GROUP = 'HIX'"
- 错误:
strSQL = "SELECT * FROM [Sheet1$A1:AI65437] WHERE GROUP = 'HIX'"
运行时错误 '-2147217865 (80040e37)': Microsoft Access数据库引擎无法找到对象'Sheet1$A1:AI65437'。
问题原因:调用的连接字符串中使用了旧版本的库,导致连接对象只能读取最多65536行数据。
解决方法:尝试使用新版本的库来更新连接字符串,以克服这个限制。可以尝试使用Provider=Microsoft.ACE.OLEDB.16.0
来替代Provider=Microsoft.ACE.OLEDB.12.0
。
在问题解决过程中,有些人提到了16.0
版本的OLEDB驱动程序可能不存在的问题,并且该版本号与Microsoft Office的年份号不一致。但是有人尝试使用16.0
版本的驱动程序后,发现这个问题得到了解决。
另外,还有人遇到了文件类型为xlsm的情况下连接出错的问题。最终发现是在SQL查询语句中不能同时指定行数和列数,只能指定列数。这可能是驱动程序的一个bug。
总结起来,解决这个问题的方法就是更新连接字符串中的库版本,使用Provider=Microsoft.ACE.OLEDB.16.0
来替代Provider=Microsoft.ACE.OLEDB.12.0
。同时,在SQL查询语句中只指定列数而不指定行数。