Export Large Recordset from Access to Excel 1

[Bjornson15]

I have been trying to do this for sometime now. I have code that "works" but it takes too long to come back.

I am trying to download a Access query into an array. I cannot take less data than what I am grabbing.

Here is my code:

Function GET_ACCESS_DATA(SQLSTRING As String)
Dim QD As QueryDef, RS As Recordset, RCD As Long
Dim i As Long, j As Long
Dim INFO, DT() As Variant

Set QD = DB.CreateQueryDef("", SQLSTRING)
Set RS = QD.OpenRecordset()
On Error GoTo NO_DATA
RS.MoveLast
RCD = RS.RecordCount
RS.MoveFirst
INFO = RS.GetRows(RCD)

ReDim DT(UBound(INFO, 2) + 1, UBound(INFO, 1) + 1)
For i = 1 To UBound(INFO, 1) + 1
For j = 1 To UBound(INFO, 2) + 1
DT(j, i) = INFO(i - 1, j - 1)
Next j
Next i

GET_ACCESS_DATA = DT
Exit Function

NO_DATA:
ReDim DT(1, 1)
DT(1, 1) = "NO DATA"
GET_ACCESS_DATA = DT
End Function

I connect to the database before this point (this does not slow anything down).

The issue is: INFO = RS.GetRows(RCD)

It just takes too long to "load" the data. Is there any other code that can bring back the data quicker? (It takes this line of code about 3-5 mins to "load" for appro 50,000 rows of data) The table is indexed properly

Thanks for any advise you may have. I will be more than willing to get more specific if need be.

Tom

 

[RoyVidar]

My question would by why stuff the contents of the recordset into an array? Why not work with the recordset?

Since this is a coding issue, perhaps forum707 (Office VBA) or one of the seven dedicated Access fora are more appropriate?

Roy-Vidar

 

[Bjornson15]

I reposted in forum707. Thanks for I didn't know exactly where to post this question because it falls within 3 Office programs (VBA, Excel and Access).

You may be able to help really quick though. Can you work with a recordset the same as an array?

Thanks for guiding me in the right direction (both in the posting and the coding).

Tom

 

[RoyVidar]

Yes, kind of, here's small (typed) sample (though, I should probably have posted it in the other forum ...)

[tt]dim l as long
for l = 0 to rs.fields.count-1
debug.print rs.fields(l).name,
next l
debug.print
do while not rs.eof
for l = 0 to rs.fields.count-1
debug.print rs.fields(l).value,
next l
debug.print
rs.movenext
loop[/tt]

But often the recordset approach - doing lot of stuff with recordsets, are slower than direct query approaches, that is, if you can work with queries in stead (but - recordsets will usually outperform arraystuff - not only because the transferring of information between the recordset and the array takes time ...)

Then - also - if you're just getting the data into excel, you could investigate also the .CopyFromRecordset method of the Excel Range object, which accept recordsets - or some of the Docmd.Transfer<methods> of Access.

Roy-Vidar

 

[RoyVidar]

Btw - here's a writeup on the .CopyFromRecordset method, both ADO and DAO

http://www.erlandsendata.no/english/index.php?d=envbadacimportado

http://www.erlandsendata.no/english/index.php?d=envbadacimportdao

Roy-Vidar

 

[pbrodsky]

You don't explain what you are doing with the data once you retrieve it or why you need to download all of it. That could make a difference in what approach to take.

Do you operate on 1 record at a time? Do you need to compare values between different records? etc.

 

[Bjornson15]

When I get the data into the array, I match the first field of the array (which is a SKU number) to a list of numbers (up to 65000) in Excel and "paste" down the rest of the data in the array.

So going one record at a time is not an option.

Vidar- Thank you for your responses. Unfortunately some things that "need" to be done have come up here at work. I will not get to fixing this issue until later this week. I will keep you posted on the method i finally take.

Tom