VBA ADODB recoredset limits

[bolo]

Hi all, I have witten a small macro that will connect to an access database, use SQL to connect to a table and download the contents of said table into a spreadsheet. As the tables might be greater than 65536 rows (yes still using xl2003 here!!!!) i have used the getrows function to split the recordset and paste the data into separate sheets as necessary. The macro works fine, unless the table in question has more than 1,000,000 records. Then i get a runtime error 1004. I do not know the exact figure, but it seems to be 1 million. So is there a limit to using the adodb recordset and getrows function? Is it a memory issue?

Here is the code...

    Set tmpApp = New Excel.Application
    tmpApp.DisplayAlerts = False
    nSheets = WorksheetFunction.RoundUp(rs.RecordCount / nRec, 0)
    tmpApp.SheetsInNewWorkbook = nSheets
    Set tmpWb = tmpApp.Workbooks.Add
    ReDim tmpTit(rs.FIELDS.Count) As String
    For i = 1 To rs.FIELDS.Count
        tmpTit(i - 1) = rs.FIELDS(i - 1).Name
    Next i
    
    For i = 1 To nSheets
        Application.StatusBar = "Downloading page " & i & " of " & nSheets & "..."
        tmpWb.Sheets(i).[a1].Resize(1, rs.FIELDS.Count) = tmpTit
        tmpArray = rs.GetRows(nRec)
        tmpWb.Sheets(i).[a65536].End(xlUp).Offset(1, 0).Resize(UBound(tmpArray, 2) + 1, rs.FIELDS.Count) = TransposeDim(tmpArray)
    Next i

TransposeDim is just a function used to transpose the array, as application.transpose has limits to transposing lots of data...

Many Thanks

Bolo.

 

[bolo]

That number does look suspciously close to 1 megabyte (1048576) so that might have something to do with it. I don't think it's necessarily the number of records because as I mentioned I tested this code with over 2 million records but it might have to do with the size of each record as you mentioned - the 2 million records I tested contained only 2 fields (1 long integer and 1 255 varchar) so your 15 fields might be causing the issue. To help you further with this can you provide the field specifications of your table i.e. data type and length so that I can set up a similar scenario for testing?

Cheers
DK

Hi Dk thanks for your continued help on this.

I just created a database with 1 autonumber column and 14 long integer columns...
I too initially thought that 1 megabye would be the problem but obviously not if yours worked...

I just deleted 13 columns and tested it again.Got the same error message Maybe it is my laptop, but it has plenty of memory..... (4GB). Iam stumped as to why this error is occuring on my runs and not yours!

 

[RoryA]

Which version of the ADO library are you using as a matter of interest?

 

[bolo]

Which version of the ADO library are you using as a matter of interest?

Hi Rorya, where do i find out such info?

 

[RoryA]

When you set the reference, it would be to the 'Microsoft ActiveX Data Objects 2.n library' - what is n?

 

[bolo]

When you set the reference, it would be to the 'Microsoft ActiveX Data Objects 2.n library' - what is n?

Doh should have guessed!

2.6 here...

 

[RoryA]

OK, and it's Access 2003 and Excel 2003? Can you also post the connection string you are using just to check?

 

[bolo]

OK, and it's Access 2003 and Excel 2003? Can you also post the connection string you are using just to check?

Yep...
THe database is in Access 2000 format if that helps...

Here is the connection string code

    Set dbConn = New ADODB.Connection
    'Set dbConn = CreateObject("ADODB.Connection")
    'dbConn.CursorLocation = adUseClient
    dbConn.Provider = "Microsoft.Jet.OLEDB.4.0"    'Connection to an mdb database.  Can be modified for many other sources
    dbConn.ConnectionString = ThisWorkbook.Path & "\" & Sheet1.[b1]
    
    'dbConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & ThisWorkbook.Path & "\" & Sheet1.[b1]
    dbConn.Open

Thanks for your help.

 

[RoryA]

When I test on WinXP using Excel 2003 and Access 2003, I get error messages about lack of resources at about 1,041,684 each time. Perhaps that is your issue.

 

[bolo]

When I test on WinXP using Excel 2003 and Access 2003, I get error messages about lack of resources at about 1,041,684 each time. Perhaps that is your issue.

thanks. glad it is not just me! dk must have a super computer! or use office 07. I will give office07 a try when I get time.

many thanks

bolo

 

[dk]

When I ran the test it was on Office 2010 and Windows 7 32bit. There is a Windows XP machine with Office 2003 a few cubicles down so I'll test later (out of curiosity as much as anything).