Speeding up Access query to Excel

[Vbanoob98]

Hi guys,

I have the following code

Sub test((
'This macro imports query into Excel

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim myValue As Variant
Dim strSQL As String

DBPass = InputBox("Enter Password")
Set dbs = OpenDatabase("U:\TEST1\test.accdb", False, False, ";pwd=" & DBPass & "")
'Get the parameter query
Set qdf = dbs.QueryDefs("Query_test1")


myValue = InputBox("Enter Code")

'Supply the parameter value
qdf.Parameters("Enter Code") = myValue


'Open a Recordset based on the parameter query
Set rst = qdf.OpenRecordset()

 While Not rst.EOF
         Sheets.Add.Name = "test"
         With ActiveSheet.Range("A1") 'create field headers
           lOffset = 0
           For Each fld In rst.Fields
               .Offset(0, lOffset).Value = fld.Name
               lOffset = lOffset + 1
           Next fld
         End With
         ActiveSheet.Range("A2").CopyFromRecordset rst, 150000
     Wend

MsgBox "Access query export completed."
End Sub

I'm just wondering if there is a faster way to do the same. Maybe without using a loop? Its currently taking 1min

 

[ranman256]

copyFromRecorset, is much faster than anything else.
tho, you dont need the while Not rst.eof.
no need to loop it.

 

[Vbanoob98]

Without the loop how do I get the header fields?

 

[xenou]

I don't think there's anything you can do codewise to speed it up. You may investigate the query itself (depending on a number of factors) to see if it can be made more efficient (but if the query is just select * from table then it can't really be improved per se). Also location, speed of network, local vs. remote are things that effect query speed.

 

[ranman256]

you already loop the fields.
you don't have to loop the rst.

 

[xenou]

Oh I see, yes why are you looping the recordset ...
Anyway, it seems if so you have a very large amount of data so that's the main thing anyway (each loop is writing 150,000 records). But of course if you can write all the records at once then go ahead and do that. Not sure how much time is consumed here just writing data vs. retrieving data ... but with a lot of data presumably both take some time.