Click here to Skip to main content
11,581,186 members (64,182 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ADO Access VBA
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

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
Edited 3-May-12 23:44pm
v3
Rate this: bad
good
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;\"";


---===EDIT===---
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)
  Permalink  
v3
Comments
ajeanson at 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 at 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 at 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 at 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 at 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 at 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
good
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

        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...
  Permalink  
v2
Comments
losmac at 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
0 OriginalGriff 260
1 Frankie-C 145
2 Sergey Alexandrovich Kryukov 135
3 RyanDev 84
4 F-ES Sitecore 80
0 OriginalGriff 1,183
1 Sergey Alexandrovich Kryukov 825
2 Abhinav S 597
3 F-ES Sitecore 500
4 Suvendu Shekhar Giri 433


Advertise | Privacy | Mobile
Web03 | 2.8.150603.1 | Last Updated 4 May 2012
Copyright © CodeProject, 1999-2015
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