dhoule
asked on
Dynamic Stored Procedure reports
The following code clip is something that I have in place to generate a report based on sent parameters to a stored procedure. The difficulty I am having is that the stored procedure is quite complex and takes about 10 seconds to execute. When the sp is changed, it seems that Access is re-compiling the query before actually running it. The amount of time it takes to 're-compile' is directly in proportion to the length of time it takes to run the actual query. the problem I have is that Access runs the routine to print the report before the query is 're-compiled' and executed, thus causing an error. Is there any way force Access to re-compile the sp, or have a smart waiting period time that would only release when the process is complete? I simple for/next loop (as you see below) is too kludgy. By the way, DoEvents does not seem to acheive this for me.
Private Sub cmdBlockListbyPort_Click()
On Error GoTo Err_cmdBlockListbyPort_Cli ck
Dim a As Long
Dim rpt As Report
Dim db As Database
Dim qryDef As QueryDef
Dim QryName As String
Dim NumCopies, X As Integer
Dim frm As Form, ctl As Control
Dim varItem As Variant
Set frm = Forms!frmReports
Set ctl = frm!lstbPortfolioName
If ctl.Selected(0) Then
If MsgBox("You cannot select All Portfolios for this report", vbOKOnly, "Report Error") = vbOK Then
Exit Sub
End If
Else
For Each varItem In ctl.ItemsSelected
Set db = CurrentDb()
QryName = "spBlockListbyPortfolio"
On Error Resume Next
db.QueryDefs.Delete QryName
On Error GoTo 0
Set qryDef = db.CreateQueryDef(QryName)
qryDef.Connect = "ODBC;DATABASE=Blotter;DSN =Blotter"
qryDef.ReturnsRecords = True
qryDef.SQL = "EXEC Blotter..Q_ReportBlockList '" & Forms!frmTradingRoom!txtFo rDate & "', '" & ctl.ItemData(varItem) & "'"
qryDef.Close
DoCmd.Hourglass True
For a = 1 To 10000
DoEvents
Next a
Call PrintReport("rptBlockList" , NumCopies)
DoCmd.Hourglass False
Next varItem
End If
Exit_cmdBlockListbyPort_Cl ick:
Exit Sub
Err_cmdBlockListbyPort_Cli ck:
MsgBox Err.Description
Resume Exit_cmdBlockListbyPort_Cl ick
End Sub
Private Sub cmdBlockListbyPort_Click()
On Error GoTo Err_cmdBlockListbyPort_Cli
Dim a As Long
Dim rpt As Report
Dim db As Database
Dim qryDef As QueryDef
Dim QryName As String
Dim NumCopies, X As Integer
Dim frm As Form, ctl As Control
Dim varItem As Variant
Set frm = Forms!frmReports
Set ctl = frm!lstbPortfolioName
If ctl.Selected(0) Then
If MsgBox("You cannot select All Portfolios for this report", vbOKOnly, "Report Error") = vbOK Then
Exit Sub
End If
Else
For Each varItem In ctl.ItemsSelected
Set db = CurrentDb()
QryName = "spBlockListbyPortfolio"
On Error Resume Next
db.QueryDefs.Delete QryName
On Error GoTo 0
Set qryDef = db.CreateQueryDef(QryName)
qryDef.Connect = "ODBC;DATABASE=Blotter;DSN
qryDef.ReturnsRecords = True
qryDef.SQL = "EXEC Blotter..Q_ReportBlockList
qryDef.Close
DoCmd.Hourglass True
For a = 1 To 10000
DoEvents
Next a
Call PrintReport("rptBlockList"
DoCmd.Hourglass False
Next varItem
End If
Exit_cmdBlockListbyPort_Cl
Exit Sub
Err_cmdBlockListbyPort_Cli
MsgBox Err.Description
Resume Exit_cmdBlockListbyPort_Cl
End Sub
ASKER
This does not help (unless I do not know how to use it). By setting this, the code after the 're-compile' will still continue to run - causing an error. I need a way to NOT run the report until the query has been evaluated and has returned values.
ASKER
This solution does not solve my problem.
You do not execute the sp
try qdDef.execute before closing it.
try qdDef.execute before closing it.
ASKER
I tried that, I get an error that states "Runtime error 3065: you cannot execute a select query."
It is exactly that...a select query...that is the query that the report is based on...
Is there another way?
It is exactly that...a select query...that is the query that the report is based on...
Is there another way?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use the parameter TIMEOUT in your ODBC connection.
bye,