Posible to speed up query update with Apllication.screenupdating?

[Guest]

I have a 8 sheets with each 8 queries.

I use this macro to actualize all queries:

Sub ActualizarConsultas()
Dim QueryTables As QueryTables
Dim query As QueryTable
Dim ws As Worksheet
Dim i As Integer
Dim vArray As Variant
Dim dteFecha As Date
Dim j As Long

With Application
' .ScreenUpdating = False
.Calculation = xlCalculationManual
End With
'Define names of sheets with queries to be updated
vArray = Array("Brand1", "Brand2", "Brand3", "Brand4", "Brand5", "Brand6",
"Brand7", "Brand8")

'Update queries
For i = 0 To UBound(vArray)
Set ws = Worksheets(vArray(i)) 'Select sheet
For Each query In ws.QueryTables
query.Refresh BackgroundQuery:=False 'Update query
Next
Next
With Application
' .ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
'MsgBox "Terminado", vbInformation, "END"
End Sub

I am afraid to use .ScreenUpdating = True. Could that not cause one query to
overwrite rows of the query right below it because the screen does not update?

TIA

Martin

 

[Mark Lincoln]

Setting ScreenUpdating to False simply keeps Excel from showing
everything that's happening while your code is running. Setting it
back to True lets you see what's happening again. If your code doesn't
overwrite rows, ScreenUpdating won't change that.

 

[Guest]

Mark is correct: the time killer is not anything with screen updating, it is
the time taken to update all the queries.

MSQuery uses a separate database connection for each query. Each time you
establish a new connection there is a lot of overhead involved: ODBC has to
request a copy of the database structure (tables and links) and then figure
out how to optimize your query. A lot of this work is duplicated when you
have multiple queries.

Using the simple tool of MSQuery I have not found any good way to get around
this. The technically better solution is to use ADO so you can establish one
connection and hold it open as you successively run your own SQL queries.

 

[Guest]

Dear Mark and K Dales:

Thank you both for you answers. I will now simply put screenupdating to false.

I have given ADO a first try last week. This is my starting point:
- ERP System: Microsoft Navision
- SQL Server
- Excel 2003

Goal: import sales data from ERP Navision to Excel.

Current solution:
- import views from SQL (8 queries/views in each sheet ranging from column A
to column AC, column AD to AL are formulas based on the retrieved data)
- actualizing these 8 x 8 = 64 queries costs 3 minutes on our new SQL SERVER
(goes really well I believe)

Question: how could ADO improve this way of working? Should I change SQL
views/queries for an ADO VBA rutine?

Any help appreciated!

Martin
"K Dales" escribió:

 

[Guest]

Don't change anything on the database side; the only question is the most
efficient way to transfer them to Excel. ADO/ODBC basically works this way:
1) You create a connection by sending the connect string to the ODBC driver.
At this point there is a lot of overhead to be accomplished: ODBC finds your
datasource and queries it to determine the schema: the tables and views and
all the linking fields and relationships and indexes, etc. This is time
consuming so the goal is to have it happen only once for your entire process
(multiple queries).
2) You now execute the query (there are several ways in ADO but I usually
create the recordset and use the RecordSet.Open method) - ODBC passes this
request along to your database and, if needed, translates and attempts to
optimize the query. This step is performed by your database's ODBC driver so
it is dependent on the database used.
3) Finally, the resulting records are passed back to your by ODBC. Options
you can set determine whether you get the whole result set in one "chunk" or
whether you read individual records off the server one at a time, as needed.

The most important thing when running multiple related queries is to avoid
all the overhead of breaking and re-establishing your connection. Also,
related queries may run faster if run consecutively, since the optimizer may
have stored the previous query and recognizes the new one as a minor
variation, so it may not have to re-translate or optimize.

The best way to do this is to create a connection object, open it, and keep
it open until all your queries are done. If you need to use multiple calls
to a sub, or multiple subs, to accomplish this then you can make the
connection a global variable; e.g.

Public MyDBCn as ADODB.Connection

Sub MainSub()
' Initialize the connection and open it
' ConnectStr would be the connection string for your ODBC driver
Set MyDBCn = New ADODB.Connection
MyDBCn.Open ConnectStr
Call DoQuery1()
Call DoQuery2()
....
MyDBCn.Close
Set MyDBCn = Nothing
End Sub

' Now the queries:
Sub DoQuery1()
Dim MyRs as ADODB.Recordset
Set MyRs = New ADODB.Recordset
MyRs.Open(MyDBCn,"SELECT ... FROM ... WHERE ...", ...)
' Process the records; e.g. read the values and paste them into Excel cells
MyRs.Close
Set MyRs = Nothing
End Sub

This is just a shell of a procedure, but note how the connection is
established only once and then reused, and also how as a global variable it
can be held open even though I am passing control to several subs. This is
efficient since the overhead of establishing the connection occurs only once.

 

[MartinK]

Martin
I have been using such functionalities for some time noiw and I can
tell You that to speed up the querry refreshing You can also think
about executing little macro that will switch of the automatic
calculation of aworkseets. THe screenupdating set to false is
frequently used and I have to say that this is a great way to free the
processor's computing abilities.
Concerning the ADO connections. I have been using following solution:
- In Windows You create the ODBC data source in "control
panel/administrative tools/ODBC data source - you can create several
daba sources (I have been using SQL Server connections and now I am
using ORACLE one)
- In excel You create the querry with following menu "data/import
external data/create a dabase querry"
- Such solution gives a quick and efficient querries
- furthermore - I always refresh querries one after another not all at
the same time.

Greatings

Martin Konopacki

 

[Guest]

Thanks once more for your expertise and hints. I will be giving it a fresh
try next few days to see how I can optimize the updating process. Right now
updating 46 files with queries and pivot tables based on queries takes 1 hour
every morning. I programmed a scheduled task that opens up an Excel file with
a worksheet that holds all names of the files to be updated.

I will let you know in the future how things work out!

Till then!

 

[Guest]

K Dales;

I just found this article and this is exactly what I am coming across-the
connections are consistenly being opened and closed in my UDF.

I guess that your suggested solution would be good if the event was just
triggered once, as opposed to being part of a function that recalculates
whenever excel needs to recalculate, right.

Steven