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_Click
    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!txtForDate & "', '" & 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_Click:
    Exit Sub

Err_cmdBlockListbyPort_Click:
    MsgBox Err.Description
    Resume Exit_cmdBlockListbyPort_Click
   

           
End Sub
dhouleAsked:
Who is Participating?
 
JeroenWConnect With a Mentor Commented:
Usually, when I want to open a recordset based on the results of a stored procedure, I have to declare a querydef and set it the way you do, and declare a recordset. The recordset is opened like this:
Set rs = qd.openrecordset(dbOpenSnapshot or dbOpenDynaset)
( the qdDef.close can than be removed, when finished with the recordset, that can be closed.)

However, from within ms Access, you can't base a report on the resultset of a stored procedure unless you put the results in a temporary-table and base the report on that table. I thought you used the querydef to do that, but it's, as you say, a select.

Okay, here's the story:
Depending whether you want the stored procedure filling the table or do it yourself by DAO, you must create a temporary table on SqlServer, respectively msAccess.

if you use a SqlServer-table, you can redefine the sp to insert the results in that table (maybe you want to empty it first...)
sp:
DELETE FROM temporarySqlTable
INSERT INTO temporarySqlTable
SELECT Blablabla FROM BlaBlaBla

msAccess:
Set qryDef = db.CreateQueryDef(QryName)
qryDef.Connect = "ODBC;DATABASE=Blotter;DSN=Blotter"
qryDef.ReturnsRecords = True
qryDef.SQL = "EXEC Blotter..Q_ReportBlockList '" &   
   Forms!frmTradingRoom!txtForDate & "', '" & ctl.ItemData
   (varItem) & "'"
qryDef.execute

The recordsource of the report should now be the temporarySqlTable


The other option (TemporaryAccessTable):
sp doesn't change.

msAccess:
'Empty the temporary table
docmd.runsql "DELETE FROM TemporaryAccessTable"

'Open the recordset
Set qryDef = db.CreateQueryDef(QryName)
qryDef.Connect = "ODBC;DATABASE=Blotter;DSN=Blotter"
qryDef.ReturnsRecords = True
qryDef.SQL = "EXEC Blotter..Q_ReportBlockList '" &   
   Forms!frmTradingRoom!txtForDate & "', '" & ctl.ItemData
   (varItem) & "'"
Set rs = qryDef.openRecordset(dbOpenSnapshot)
If not rs.eof then
    Set rsTempTable = db.openrecordset("TemporaryAccessTable",
      dbOpenDynaset or dbOpenTable)

    While not rs.eof
        rsTempTable.addnew
        rsTempTable!a = rs!a
        ...
        rsTempTable!z = rs!z
        rsTempTable.update
        rs.movenext
    wend
    rsTempTable.close
endif
rs.close

Call PrintReport("rptBlockList", NumCopies)


Hope this helps,

Jeroen.
0
 
csalvesCommented:
Hello,
You can use the parameter TIMEOUT in your ODBC connection.

bye,
0
 
dhouleAuthor Commented:
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.  
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dhouleAuthor Commented:
This solution does not solve my problem.

0
 
JeroenWCommented:
You do not execute the sp

try qdDef.execute before closing it.
0
 
dhouleAuthor Commented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.