Why is creating this query with code slow?

  • Thread starter Thread starter Chuck
  • Start date Start date
C

Chuck

I meant to cross post this in microsoft.public.access.modulesdaovba.ado but
did seperate posts by mistake. I apoligize in advance. I think I'll have a
better chance of getting an answer here.


I am using this code to create a query object from an SQL statement using
ADO. When I click on the button which executes the code, it takes between
10 and 20 seconds for the query to be created. Does anyone have any idea
why?

Thanks

************

Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
DoCmd.Hourglass True

Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command

cat.ActiveConnection = CurrentProject.Connection
Set cmd.ActiveConnection = cat.ActiveConnection

cmd.CommandText = OpenArgs
cat.Procedures.Append "~SQL_Analysis_query", cmd

Set cat = Nothing
Set cmd = Nothing
 
I replaced the whole darn thing with the code below and it works much
better.

Set ADOConnection = CurrentProject.Connection
strEXECUTE = "CREATE PROCEDURE zReference_SQL_Analysis_query AS " &
OpenArgs
ADOConnection.Execute (strEXECUTE)
 
Chuck said:
I meant to cross post this in
microsoft.public.access.modulesdaovba.ado but did seperate posts by
mistake. I apoligize in advance. I think I'll have a better chance
of getting an answer here.


I am using this code to create a query object from an SQL statement
using ADO. When I click on the button which executes the code, it
takes between 10 and 20 seconds for the query to be created. Does
anyone have any idea why?

Thanks

************

Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
DoCmd.Hourglass True

Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command

cat.ActiveConnection = CurrentProject.Connection
Set cmd.ActiveConnection = cat.ActiveConnection

cmd.CommandText = OpenArgs
cat.Procedures.Append "~SQL_Analysis_query", cmd

Set cat = Nothing
Set cmd = Nothing
Change

cat.ActiveConnection = CurrentProject.Connection
Click to expand...

to

Set cat.ActiveConnection = CurrentProject.Connection

As it is, you're opening a new connection rather than reusing the
existing one.
 
Back
Top