Why is creating this query with code slow?

[Chuck]

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

 

[RobFMS]

This statement:

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

will eat up a lot of time. Is it possible for you to create a global
variable for the ActiveConnection and initialize it at the opening of your
application? Most people expect that the opening of the application might
take a few extra seconds because its "setting things up". However, they are
least likely to be patient if a button is clicked and they have to wait.

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Chuck]

Thanks but it did not make a difference. I modified the code as follows to
see where things appeared to slow down:

Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
Set cmd.ActiveConnection = cat.ActiveConnection
msgbox "1"
cmd.CommandText = OpenArgs
msgbox "2"
cat.Procedures.Append "~SQL_Analysis_query", cmd
msgbox "3"
Set cat = Nothing
Set cmd = Nothing


The first two message boxes appear fairly quickly but it takes about 20
seconds for the last one to appear. It looks like < cat.Procedures.Append
"~SQL_Analysis_query", cmd > is slowing things down. Any ideas on how I can
make it faster?

 

[Chuck]

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)

 

[Ron Weiner]

Chuck

In the past I have found that anything I ever wanted to do with the ADOX
library too a long time to execute. It may just be that there is nothing
you can do to speed this up.

However, it occurs to me that it may be possible to use SQL DDL and the
standard ADO connection and or command objects to add / Alter Stored
procedures. I'm thinking code like:

Set cn = New ADODB.Connection
cn.ConnectionString = strCon ' Your Connection String
cn.Open
cn.Execute(strSql) ' strSql is the DDL that creates
the Proc
cn.Close
Set Cn = Nothing

Aint never done it, but at least in theory it should work and should be
reasonably fast. Much faster than the ADOX stuff. Let me know how you make
out.

Ron W