Solved

Dynamic Stored Procedure reports

Posted on 1998-11-09
6
346 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

737 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