?
Solved

Dynamic Stored Procedure reports

Posted on 1998-11-09
6
Medium Priority
?
348 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 100 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

801 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