源文件具有超过32k行时Microsoft.ACE.OLEDB.12.0的问题 [英] Problem with Microsoft.ACE.OLEDB.12.0 when source file has more than 32k rows

问题描述

我正在Access 2007中使用vba代码读取Excel文件来馈送Access表:

Hi,

I''m using vba code in Access 2007 to read excel files to feed Access tables:

Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & source & ";Extended Properties=Excel 8.0;"
    .Open
End With
Set rsEX = New ADODB.Recordset
rsEX.Open ("SELECT * FROM [TMP_PARExport]"), cn
While Not rsEX.EOF
    'some code here
    rsEX.MoveNext
Wend

这段代码很好用，然后我可以使用记录集读取数据并浏览记录，等等.

我的问题:
如果excel文件的行数超过32k(TBD的精确行数)，则它不再起作用，我必须使用Windows Task Manager手动停止代码.
注意:每个Excel文件中的数据类型和格式完全相同，只是行数或行数不同.

我找不到任何解决此问题的方法，将不胜感激.
我的Windows XP Pro 2002版本带有sp3.

This code works great, I can then use recordsets to read the data and navigate through the records, etc

My problem:
if the excel files has more than 32k rows (precise number of rows TBD), it doesn''t work anymore, I have to manually stop the code with windows task manager.
Note: the data in each excel file is exactly of the same type and same format, just the number or rows is different.

I couldn''t find anywhere a solution to this problem, any help will be much appreciated.
I have windows xp pro 2002 with sp3.

推荐答案

阅读 ^ ]讨论.这可能会有所帮助.

Read this[^] discussion. It might be helpful.

sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0;\"";

--- ===编辑=== ---
使用这个:

---===EDIT===---
Use this:

sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0;HDR=YES; IMEX=0;"""

Recordset.EOF属性的有趣行为...
尝试打开具有不同参数的记录集:

Interesting behavior of Recordset.EOF property...
Try to open recordset with different parameters:

rst.Open strSQL, sConStr, adUseClient, adOpenStatic, adCmdTable

有关Recordset，BOF，EOF的更多信息，请访问:
记录集对象基础 [记录集对象(ADO)
BOF，EOF属性(ADO)
BOF，EOF属性示例(ADO)

More about Recordset, BOF, EOF at:
Recordset object Basics[^]
Recordset Object (ADO)
BOF,EOF Properties (ADO)
BOF,EOF Properties Example (ADO)

我的铅的解决方案:

如果行数超过32K，则似乎没有EOF.
要检测EOF并退出"while"循环，我需要检查excel记录集中的数据是否不为null.

Solution to my pb:

It seems that there is no EOF if they are more than 32K rows.
To detect the EOF and exit the ''while'' loop I need to check if the data in the excel record set is not null.

stillData = True
While (Not rsEX.EOF And stillData)
    If IsNull(rsEX!Date) Then stillData = False
    'code here

    rsEX.MoveNext
Wend

感谢所有的帮助者


PFFF正在变得烦人:现在我想在创建记录集的SQL请求中添加``where''子句.
如果源文件少于32k行，则再次为0 pb.
如果源文件中的行数超过32 k，并且"where"子句仅返回其中的几行(在我的情况下为10)
那么当我到达10条记录中的最后一条时，rsEX.movenext将不起作用. (在使用rsEX.movenext之前，我有rsEX.EOF = false)
这真的很痛苦...

thanks to all the helpers


Pfff it''s getting anoying: now I want to add a ''where'' clause in the SQL request creating the recordset.
if the source file has lesss than 32k rows, again 0 pb at all.
if the source file has more than 32 k rows, and the ''where'' clause only returns a few of them (in my case:10)
then the rsEX.movenext doesn''t work when I reach the last of the 10 records. (before using rsEX.movenext, I have rsEX.EOF = false)
this is really a pain...

这篇关于源文件具有超过32k行时Microsoft.ACE.OLEDB.12.0的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！