3

I wrote a simple VBA code to run multiple SQL queries. The results are supposed to go to excel sheet. The problem is that the procedure takes ages to finish! In fact it would be much faster to run these queries one by one. Can anyone please tell me how to make it work faster?

Here is my code:

Const strCon As String = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=xxxx)(PORT=xxx))" & _
"(CONNECT_DATA=(SID=RTD))); uid=xxxx; pwd=xxxx;"

Sub RunScripts()

Dim r As Integer
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim q1 As String
Dim q2 As String
Dim q3 As String

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

q1 = Worksheets("Data").Range("i28").Value
Set rs = con.Execute(q1)
Worksheets("ACCV Query").Range("A2").CopyFromRecordset rs

Set rs = Nothing
r = Worksheets("ACCV Query").Range("A65536").End(xlUp).Row

'Next SQL Query
q2 = Worksheets("Data").Range("j28").Value
Set rs = con.Execute(q2)
Worksheets("ACCV Query").Range("A" & r).CopyFromRecordset rs

Set rs = Nothing
r = Worksheets("ACCV Query").Range("A65536").End(xlUp).Row

'Next SQL Query
q3 = Worksheets("Data").Range("k28").Value
Set rs = con.Execute(q3)
Worksheets("ACCV Query").Range("A" & r).CopyFromRecordset rs

con.Close
Set con = Nothing
Set rs = Nothing

End Sub

The SQL queries are stored in cells and named q1, q2, etc. (all of them work).

When I ran two of such queries it worked fine, but once I launched 15... the macro never finished working. Each SQL script takes couple of seconds to run so it should work quick.

How can I make it work faster? Help please!

Thanks a lot! M.

Improve this question
4
  • Is it possible that you're hitting the row limit in Excel? If you're using 2003, the limit is 65k rows. Commented Oct 9, 2012 at 16:39
  • Check each query individually. Nothing in your code indicates that it would hang Commented Oct 10, 2012 at 0:13
  • Thank you for the comments. @Pynner The queries work - I checked each o them manually and they're fine. When I run just two of them - it works great, the problem occurs when there are more - like 10 or so. Commented Oct 10, 2012 at 10:42
  • @Jim Unfortunately that is not the case, the result of all queries combined is no more than 300 rows:( Thank you for the comment! Commented Oct 10, 2012 at 10:43

1 Answer 1

Reset to default
3

Typically what slows Excel down is the constant screen updating and recalculation. Whenever you change the contents of a cell, it recalculates and refreshes the screen. On big insertions, this really slows the operation down. Fortunately you can turn this off.

At the top of your function, put:

Application.Calculation = xlManual
Application.ScreenUpdating = False

Then at the end of your function (and in any error handling), put:

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks Chris! I forgot about that. Added just now, i will run a check and see if it helped.
Unfortunately this was not enough, still with 15 scripts the macro never stops running:(
I noticed that the size of the recordset makes the difference - if there is a lot of rows returned from a particular query - this is where the macro would "hang". I have "Set rs = Nothing" but maybe I should add something else? Other thing is that I have "Set rs = New ADODB.Recordset" only once, on the top - perhaps it should be added before each query - can that make a difference?
Have you run it using the debugger to see what lines are causing the delay?
Hi Chris, No I haven't, I'm not that familiar with debugger.. never used it before. I'll try it today! Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.