Slow execution of stored procedure in VBA

[hedgracer]

Here is the code:

Private Sub cmdProcessePacketGenerationTables_Click()
Dim x As Variant
If strYYYYMMDD_end = "" Then
x = MsgBox("Please select or reselect the Month dropdown list", vbCritical, "Select Month")
Exit Sub
End If
DoCmd.Hourglass True

strCnxn = set_connection

Set cnxn = New ADODB.Connection
cnxn.ConnectionTimeout = 0
cnxn.CommandTimeout = 0

cnxn.Open strCnxn

strSQL_Execute = "dbo.Actg_Financial_Packet_Generator_Process " & strYYYYMMDD_end

'strSQL_Execute = "dbo.Actg_Financial_Packet_Generator_Process "

cnxn.Execute strSQL_Execute, , adExecuteNoRecords

DoCmd.Hourglass False

DoCmd.OpenForm "Unallocated_Reg_Dept"
End Sub

This is an Access 2007 database. I am running it as a single user front end with linked sql server 2005 tables. The problem is that when I execute dbo.Actg_Financial_Packet_Generator_Process through the Access 2007 front end it takes about 3-4 minutes to complete. Executing the same stored procedure on SQL Server 2005 the stored procedure completes in 1:13. Why is this happening? We recently upgraded to 2007 from 2003 and this is one of the many issues that have surfaced since the upgrade. Any help is greatly appreciated. Thanks.

Dave

 

[SkipVought]

What happens if you execute the stored procedure from the query itself in Access?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I typically create a pass-through query with all the appropriate connection properties. Then change the SQL property of the saved P-T query to the desired string. You can then use code to open the query.

Duane
Hook'D on Access
MS Access MVP

 

[hedgracer]

When executing the stored procedure through a pass thru query the procedure executed in 1:25. Why would this be faster than in VBA?

Dave

 

[dhookom]

No clue since you haven't provided all of your code.

Duane
Hook'D on Access
MS Access MVP

 

[hedgracer]

The actual VBA code is above. The call to the stored procedure in the pass thru query is:

EXEC dbo.Actg_Financial_Packet_Generator_Process "20100831"

The earlier question still stands. In addition, how do you pass the parameter to the pass thru query?

Dave

 

[SkipVought]

The QUERY SQL, please, that is in the SP.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I change the SQL property with code link thread701-1618944.

We don't know anything about set_connection.

Duane
Hook'D on Access
MS Access MVP