VBA: Querying Access with Excel. Why so slow?

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? :-)

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.