VBA: Querying Access with Excel. Why so slow?

Question

I found this code online to query Access and input the data into excel (2003), but it is much slower than it should be:

Sub DataPull(SQLQuery, CellPaste)
Dim Con As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim DBlocation As String, DBName As String
Dim ContractingQuery As String

If SQLQuery = "" Then

Else
    DBName = Range("DBName")
    If Right(DBName, 4) <> ".mdb" Then DBName = DBName + ".mdb"

    DBlocation = ActiveWorkbook.Path
    If Right(DBlocation, 1) <> "\" Then DBlocation = DBlocation + "\"

    Con.ConnectionString = DBlocation + DBName
    Con.Provider = "Microsoft.Jet.OLEDB.4.0"
    Con.Open

    Set RST = Con.Execute(SQLQuery)
    Range(CellPaste).CopyFromRecordset RST

    Con.Close
End If

End Sub

The problem is that this code takes very long. If I open up Access and just run the query in there it takes about 1/10th the time. Is there anyway to speed this up? Or any reason this might be taking so long? All my queries are simple select queries with simple where statements and no joins. Even a select * from [test] query takes much longer than it should.

EDIT: I should specify that the line

Range(CellPaste).CopyFromRecordset RST

was the one taking a long time.

Dayan

When run in the step through mode, which line of code takes longer? – Manuel

Range(CellPaste).CopyFromRecordset RST – Dayan

How many records do you retrieve? – Fortify

Hmm. That changes everything. I don't think you'll get any faster than CopyFromRecorset. Also, you should seriously consider renaming this thread and deleting all the irrelevant stuff in your post. Access clearly has nothing to do with the problem, IMHO. – Newsom

Answer 1

Lots of formulas may reference the query. Try temporarially turning on manual calculate in the macro and turning it off when all of your queries are done updating.

This should speed it up a bit, but still doesn't fix the underlying problem.

Dayan

Answer 2

I'm no expert, but I run almost exactly the same code with good results. One difference is that I use the Command object as well as the Connection object. Where you

Set RST = Con.Execute(SQLQuery)

I

Dim cmd As ADODB.Command
Set cmd.ActiveConnection = con
cmd.CommandText = SQLQuery
Set RST = cmd.Execute

I don't know if or why that might help, but maybe it will? :-)

Newsom

Answer 3

I don't think you are comparing like-with-like.

In Access, when you view a Query's dataview what happens is:

• an existing open connection is used (and kept open);
• a recordset is partially filled with the first few rows only (and kept open);
• the partial resultset is shown in a grid dedicated to the task and optimized for the native data access method Access employs (direct use of the Access Database Engine DLLs, probably).

In your VBA code:

• a new connection is opened (then later closed and released);
• the recordset is fully populated using all rows (then later closed and released);
• the entire resultset is read into a Excel's generic UI using non-native data access components.

I think the most significant point there is that the dataview in Access doesn't fetch the entire resultset until you ask it to, usually by navigating to the last row in the resultset. ADO will always fetch all rows in the resultset.

Second most significant would be the time taken to read the fetched rows (assuming a full resultset) into the UI element and the fact Excel's isn't optimized for the job.

Opening, closing and releasing connections and recordsets should be insignificant but are still a factor.

I think you need to do some timings on each step of the process to find the bottleneck. When comparing to Access, ensure you are getting a full resultset e.g. check the number of rows returned.

Tuchman

Answer 4

I would recommend you to create the Recordset explicitly rather than implicitly using the Execute method. When creating explicitly you can set its CursorType and LockType properties which have impact on performance.

From what I see, you're loading data in Excel, then closing the recordset. You don't need to update, count records, etc... So my advice would be to create a Recordset with CursorType = adOpenForwardOnly & LockType = adLockReadOnly:

...
RST.Open SQLQuery, Con, adOpenForwardOnly, adLockReadOnly
Range(CellPaste).CopyFromRecordset RST
...

Recordset Object (ADO)

Malaria

I gave this a try. It worked but did not lead to a difference in runtime. Thanks anyway. – Dayan

Answer 5

Since you're using Access 2003, use DAO instead, it will be faster with the Jet engine.

See http://www.erlandsendata.no/english/index.php?d=envbadacexportdao for sample code.

Note that you should never use the "As New" keyword, as it will lead to unexpected results.

Candice

This is problematic because not everyone has DAO enabled so it'll make it harder to pass this file around to different people. – Dayan

The same could apply to ADO. My understanding is that DAO is part of, or closely related to, the Jet engine used by Access 2003. So it's highly unlikely that it wouldn't be present along with Access. But you can always check the following location for it: C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll (assuming DAO 3.6) You would also need to convert your code to late bound, and only use the DAO code if the file was present. – Candice

It's also present with Excel, as the Jet db engine is a component of Office, and Excel is quite closely integrated with it. – Ezaria

The OP said, "the query in there it takes about 1/10th the time". You don't really think switching to DAO will yield a 1000% performance benefit, do you? – Tuchman

No, but all things being equal it will be faster. – Candice

@JP: Maybe so (or maybe no significant increase) but that's a distraction from, rather than an solution to, the problem here. – Tuchman

The problem is that the code is slow. I'm suggesting another method that might be faster. Instead of taking his existing code for granted and trying to make micro-optimizations on it, an entirely new approach to his goal might yield faster results. – Candice

Answer 6

I used your code and pulled in a table of 38 columns and 63780 rows in less than 7 seconds - about what I'd expect - and smaller recordsets completed almost instantaneously.

Is this the kind of performance you are experiencing? If so, it is consistent with what I'd expect with an ADO connection from Excel to an MDB back-end.

If you are seeing much slower performance than this then there must be some local environment conditions that are affecting things.

Thingumabob

Answer 7

If you retrieve a lot of records, it would explain why the Range(CellPaste)takes so long. (If you execute the query in Access it wouldn't retrieve all the records, but if you do the CopyFromRecordset it requires all the records.)

There is a MaxRows parameter for CopyFromRecordset:

Public Function CopyFromRecordset ( _
    Data As Object, _
    <OptionalAttribute> MaxRows As Object, _
    <OptionalAttribute> MaxColumns As Object _
) As Integer

Try if settings this to a low value (like 10 or so) changes the performance.

Fortify

Answer 8

What about the following turnarounds or improvements:

1. Once opened, save the recordset as xml file (rst.saveToFile xxx) and then have Excel reopen it.
2. Once opened, put recordset data in an array (rst.getRows xxx), and copy the array on the active sheet
3. And, at any time, minimise all memory / access requirements: open the recordset as read-only, forward only, close the connection once the data is on your side, etc.

Lippizaner

Answer 9

I don't know if it will help, but I am using VBA and ADO to connect to an Excel spreadsheet.

It was retrieving records lightning-fast (<5 seconds), but then all of a sudden it was awfully slow (15 seconds to retrieve one record). This is what lead me to your post.

I realized I accidentally had the Excel file open myself (I had been editing it).

Once I closed it, all was lightening fast again.

Proscenium

Answer 10

The problem 9 times out of 10 is to do with the Cursor Type/Location you are using.

Using dynamic cursors over network connections can slow down the retrieval of data, even if the query executed very fast.

IF you want to get large amounts of data very quickly, you'll need to use CursorLocation = adUseClient on your connection. This mean's you'll only have a static local cursor, so you won't get live updated from other users.

However - if you are only reading data, you'll save ADO going back to the DB for each individual record to check for changes.

I recently changed this as I had a simple loop, populating a list item, and each loop was taking around 0.3s. Not to slow, but even on 1,000 records thats 30 seconds! Changing only the cursor location let the entire process complete in under 1 second.

Raindrop