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]

Hi

Maybe I've missed something but can you not just use the CopyFromRecordset method as opposed to going from a recordset to array and then to the range?

DK

The recordset contain more records than an excel speadsheet which is why i need to cut the recordset into arrays.

 

[bolo]

Why bother? You have the data in Access. That's a better place for it than umpteen Excel worksheets.

I agree... Howeve some people are more comfortabe in Excel. Anyway it is almost there, i would just like to know the reason why the macro dies to further my understanding

 

[RoryA]

Have you stepped through the code to check all the variables in transposedim when it fails?

 

[dk]

The recordset contain more records than an excel speadsheet which is why i need to cut the recordset into arrays.

The CopyFromRecordset method allows for this - there is an optional argument that specifies how many records will be dumped. Here is an example that will import a recordset into multiple worksheets. I just tested this and successfully imported just over 2 million records into 31 worksheets within an Excel 2003 workbook.

HTH
DK

Sub ImportRecordset()

'Imports a recordset into Excel.  If the recordset contains more than lMAX_ROWS_PER_SHEET
'then the import will be split over multiple worksheets

    Const lMAX_ROWS_PER_SHEET = 65535
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim sSQL As String
    Dim lField As Long
    Dim sht As Worksheet


    Set adoConn = CreateObject("ADODB.Connection")
    adoConn.Provider = "Microsoft.Jet.OLEDB.4.0"    'Connection to an mdb database.  Can be modified for many other sources
    adoConn.ConnectionString = "C:\temp\mydb.mdb"
    adoConn.Open

    Set adoRS = CreateObject("ADODB.Recordset")

    sSQL = "SELECT * FROM MYTABLE"
    adoRS.Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly


    While Not adoRS.EOF

        Set sht = Worksheets.Add

        'Put field headers in row 1
        For lField = 0 To adoRS.Fields.Count - 1
            sht.Cells(1, lField + 1).Value = adoRS.Fields(lField).Name
        Next lField

        sht.Range("A2").CopyFromRecordset adoRS, lMAX_ROWS_PER_SHEET


    Wend

    adoRS.Close
    Set adoRS = Nothing

    adoConn.Close
    Set adoConn = Nothing


End Sub

 

[bolo]

see that why I kept asking, to learn new things that I didnt know. thanks dk will give it a go. wasn't sure how could get the record set to move to the next recordset using copyfromrecordset

many thanks.

bolo

 

[dk]

That's the great thing about all of this - always something new to learn

Just as an additional bit of info, the reason I know about this is because I had a similar issue a few years back where I to import a very large text file into Excel - that was the requirement from the business as Access was not available to everyone. Initially I used the FilesystemObject to read through the file row by row but it was quite slow and not very "neat" (although it worked). After a bit more investigation I realised that you could connect to a text file using the Access (Jet) provider - the code was simplified and the performance improvement was significant.

Anyway, let us know how you get on.

Cheers
DK

 

[bolo]

That's the great thing about all of this - always something new to learn

Just as an additional bit of info, the reason I know about this is because I had a similar issue a few years back where I to import a very large text file into Excel - that was the requirement from the business as Access was not available to everyone. Initially I used the FilesystemObject to read through the file row by row but it was quite slow and not very "neat" (although it worked). After a bit more investigation I realised that you could connect to a text file using the Access (Jet) provider - the code was simplified and the performance improvement was significant.

Anyway, let us know how you get on.

Cheers
DK

Hi DK, i tried your code, and it worked, but it too died after the same number of records. Therefore i am inclined to believe i must have hit a cell limit. My records have 15 fields so that is why i think it is a cell limit.

 

[dk]

Are you getting an error message or is it just freezing? If you get an error message what is it exactly and on what line does it occur? The only limit on how many cells can have data is determined by available memory.

DK

 

[bolo]

Are you getting an error message or is it just freezing? If you get an error message what is it exactly and on what line does it occur? The only limit on how many cells can have data is determined by available memory.

DK

I get a run-time error '-2147467259 (80004005)' error . It dies here

sht.Range("A2").CopyFromRecordset rs, nRec

It dies after copying the 1,048,305th record!

 

[dk]

I get a run-time error '-2147467259 (80004005)' error . It dies here

sht.Range("A2").CopyFromRecordset rs, nRec

It dies after copying the 1,048,305th record!

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