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.

Can I work with the recordset the same as an array?

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

Tom

 

[PHV]

Have you tried this ?
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset(SQLSTRING, dbOpenForwardOnly)
On Error GoTo NO_DATA
GET_ACCESS_DATA = RS.GetRows(&H7FFFFFF)
RS.Close
Exit Function


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bjornson15]

Thank you for you quick response.

I tested this out and the performance is the same. I believe the issue is in the fact that Office has to get to the end of the recordset to load it into the array for the GETROWS command.

If you have any more ideas, please let me know. I will post if I find the solution.

Thanks
Tom

 

[Bjornson15]

Here is the code that I am using. It isn't much quicker (about 2-3 secs) but some time is better than none. If you have any other ideas on speeding it up, I am willing to give it a try.

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

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

ReDim DT(RCD, FC)
i = 1
Do While Not RS.EOF
For l = 0 To RS.Fields.Count - 1
DT(i, l + 1) = RS.Fields(l).Value
Next l
i = i + 1
RS.MoveNext
Loop

GET_ACCESS_DATA = DT
Exit Function

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


Thanks for all your help

Tom