?
Solved

Dynamic Stored Procedure reports

Posted on 1998-11-09
6
Medium Priority
?
349 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

650 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