Why is this ADO code so slow?

[Garry Rathbone]

Hi,
I'm using ADO to create a new querydef object and finding
the ADO commands incredibly slow. I'm using Access 2002
and the code is as follows:

------------------------------------------------------
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strWorkbook As String
strWorkbook = CurDir() & "\SLTemp.xls"

On Error Resume Next
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

Set cmd = New ADODB.Command
cmd.CommandText = strSource 'SELECT statement

DoCmd.DeleteObject acQuery, "qrySLTemp"
'cat.Views.Delete "qrySLTemp"
cat.Views.Append "qrySLTemp", cmd
'cat.Views.Refresh
--------------------------------------------------------

It's the APPEND method that takes around 13 seconds to
execute. I got rid of the DELETE and REFRESH methods
because they too were taking ages.

The whole point of this is to create a query based on a
subform's recordsource that I can then pass into Excel.
It works, just very slowly!

Any help appeciated.

Garry

 

[SA]

Garry:

I've never found ADOX to be fast when changing catalog objects. One way to
speed things up a bit, is rather than to delete and append the view (query),
simply change its SQL to what you want it to be for the current session.