Click here to Skip to main content
12,820,208 members (23,885 online)
Rate this:
Please Sign up or sign in to vote.
See more: ADO Access VBA

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;"
    End With
    Set rsEX = New ADODB.Recordset
    rsEX.Open ("SELECT * FROM [TMP_PARExport]"), cn
    While Not rsEX.EOF
        'some code here

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 4-May-12 0:22am
Updated 4-May-12 0:44am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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;\"";

Use this:
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:
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)
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"
losmac 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
losmac 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
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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


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...
losmac 4-May-12 12:11pm
See my solution.

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

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170308.1 | Last Updated 4 May 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100