Solved

Dynamic Stored Procedure reports

Posted on 1998-11-09
6
340 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:dhoule
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:csalves
ID: 1967993
Hello,
You can use the parameter TIMEOUT in your ODBC connection.

bye,
0
 

Author Comment

by:dhoule
ID: 1967994
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
 

Author Comment

by:dhoule
ID: 1967995
This solution does not solve my problem.

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Expert Comment

by:JeroenW
ID: 1967996
You do not execute the sp

try qdDef.execute before closing it.
0
 

Author Comment

by:dhoule
ID: 1967997
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
 
LVL 1

Accepted Solution

by:
JeroenW earned 50 total points
ID: 1967998
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now