Speeding up Access query to Excel

Vbanoob98

Board Regular
Joined
Sep 13, 2019
Messages
96
Hi guys,

I have the following code

VBA 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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,842
copyFromRecorset, is much faster than anything else.
tho, you dont need the while Not rst.eof.
no need to loop it.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,603
Office Version
2013
Platform
Windows
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.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,603
Office Version
2013
Platform
Windows
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.
 
Last edited:

Forum statistics

Threads
1,089,644
Messages
5,409,497
Members
403,266
Latest member
HMR120

This Week's Hot Topics

Top