Problem with Microsoft.ACE.OLEDB.12.0 when source file has more than 32k rows

Question

4.00/5 (1 vote)

See more:

Hi,

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

VB

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

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.

Posted 3-May-12 23:22pm

ajeanson

Updated 3-May-12 23:44pm

v3

Add a Solution

2 solutions

Solution 1

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

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

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

VB

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

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

VB

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

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

Posted 3-May-12 23:29pm

Maciej Los

Updated 4-May-12 6:10am

v3

Comments

ajeanson 4-May-12 5:33am

i've already tried this solution but it doesn't work yet: I don't have the IMEX property available. I have a compilation error : "Pilote ISAM not found"

Maciej Los 4-May-12 6:56am

First of all: MS Excel 2007 => 12 version, not 8 as is in your example. See here for correct connection-string.
Here is a list of availible MS JET Database engine. Please check your version, then install/reinstall drivers for Access 2007.

ajeanson 4-May-12 8:02am

I changed the code to excel 12.0 and updated my drivers. Still the same pb.
Weird thing is that I dont have access to the properties IMEX nor HDR of the connection... I'm using Microsoft ActiveX Data Object 2.8 library

Maciej Los 4-May-12 8:59am

IMEX and HDR those are Extended properties. Just set them in connection-string, not inside With cn ... End With code block. See sConn after update.

ajeanson 4-May-12 9:21am

ok i've done that and it works, but I still have the same problem.
I've noticed that the problem comes from the 'While Not rsEX.EOF' loop:
it looks like it never detects the EOF if there are more than 32k records
if I add a counter in the loop and print the counter when it is equal to 35000, it works, i see the msgbox with counter = 35000. (the Excel file has 35850 records). But then, the program gets stuck and never ends, as if the while loop couldn't finish, and I have to manually force the end of VBA with windows task manager.

ajeanson 4-May-12 9:30am

ok now I think I have solved my problem: I just need to check if the values of the Excel recordset is not null and exit the loop if it is. I just did it quickly and it seems to be an efficient workaround.

But still, the main problem is weird why doesnt it detect the EOF if there are more than 32k rows? :(

Thanks a lot for your help guys

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ajeanson · Accepted Answer · 2012-05-04T03:36:00

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.

VB

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

    rsEX.MoveNext
Wend

thanks to all the helpers

EDIT:
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...