Reports and Pass Thru Queries - Report not updating

[sross81]

Hello,

I have a pass through query that executes a stored procedure that will return results that I use on an MS access report. I have created the pass through query like I do for all of my forms and instead of putting it in the record source property I put code in the load event to populate the record source. This works fine for a form....but it doesn't work for a report. If I put the name of the pass through query into the report record source property it runs, but for some reason it doesn't update if I run the report again with different criteria.

How can I have a pass through query as the record source for a report and have it update everytime I run the report again? I checked the pass through query and it just holds the first parameter I ran the report with. If I remove the pass through query from the record source properties of the report it doesn't run at all. I set a breakpoint and its like it doesn't even get to the report load event, but it will open the report it will just have nothing in it.


Private Sub Report_Load()
Dim rs As DAO.Recordset
Dim db As Database
Dim rstEncounterDetails As DAO.Recordset
Dim strEncounterEmailSQLString As String

'Set the Encounber number variable equal to the first (and only in this case) argument passed in from the measure worklist.
strProvTbleID = [Forms]![frm_SendVarianceNotice]![ProvTbleID]

'Build SQL string to pass into sp_GetWorklistAMI based on user input parameters.
strEncounterEmailSQLString = "EXEC sp_GenerateVarianceEmailMainReport " & strProvTbleID
' Set database variable to current database.
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("sp_GenerateVarianceEmailMainReport")

'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strEncounterEmailSQLString

'Allow the stored procedure to return a result set to be bound.
qdf.ReturnsRecords = True

'Open Recordset object as a query def.
Set rstEncounterDetails = qdf.OpenRecordset()

If rstEncounterDetails.EOF Then
MsgBox ("There are no variances attached to this encounter")
Exit Sub
End If
'Move to the first record of the record set.
rstEncounterDetails.MoveFirst
'Bound the current form that is loading to the recordset returned by the stored procedure containing only user's selection.
Set Me.Recordset = rstEncounterDetails

End Sub

 

[mdlueck]

It sounds like I use a quite similar approach to you for reports. In my design a Stored Procedure collects the records for a report. That SP downloads into a FE temp table. Then the report is bound to the FE temp table directly. For header fields I execute a second SP, and one-off map those fields to the header fields via VBA code. Those header fields are individual unbound fields.

When ever I run the report, I start by emptying the FE temp table. That insures that no stale data ends up on reports.

 

[sross81]

My concern with a temp table is that if multiple people run the report that the results will get overwritten. I am going to give everyone their own copy of the front end, but if the temp table is stored on the server it seems like they would all be using the same temp table. Am I wrong?

 

[mdlueck]

The temp table should ALSO be in the FE DB. Then you will have no problems.

 

[sross81]

So in my sql procedure I should insert all rows into say TempEmailList and then link to it from my front end? That is where I feel like it will get overwritten. Should the temp table exist locally in the front end? If so how do I insert the records from the stored procedure into the local table? I assume through vb. I have just never done it. I will look around some. If you have any examples I would appreciate it.

Thank you,
Sherri

 

[mdlueck]

I use my solution for downloading records into an FE temp table (the #1 example), referenced here:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

Now, in the context of reports specifically, here is how the code works which I was describing to you... one SP to collect up header fields, then another SP to populate a FE temp table with the result set:

Public Function RefreshLocalTmpTbl_Project() As Boolean
On Error GoTo Err_RefreshLocalTmpTbl_Project

  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset
  Dim strSQLbe As String
  Dim strSQLfe As String

  'Clear the report attributes
  Me.Clear

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.ADODBConnectionObj()
    .CommandText = "clsObjRptToolingDatesByProcessTbl_RefreshLocalTmpTbl_Header_Project"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@projid").Value = ObjProjectsTbl.id
    Set adoRS = .Execute()
  End With

  With adoRS
    'Fetch the values found
    Me.rptLateCount = Nz(adoRS!latecount, "0")
    Me.rptDueDate = Nz(adoRS!duedate, "N/A")

    'Close the database table
    .Close
  End With

  'See if the report found any records
  If Me.rptDueDate = "N/A" Then
    'Nope, we outtahere!
    RefreshLocalTmpTbl_Project = True
    GoTo Exit_RefreshLocalTmpTbl_Project
  End If

  'Define queries in order to execute the Stored Procedure which will populate a FE temp table
  strSQLbe = "SET NOCOUNT ON;" & vbCrLf & _
             "DECLARE @projid smallint;" & vbCrLf & _
             "SET @projid = " & ObjProjectsTbl.id & ";" & vbCrLf & _
             "EXEC dbo.clsObjRptToolingDatesByProcessTbl_RefreshLocalTmpTbl_Project @projid;"

  strSQLfe = "INSERT INTO " & Me.FETempTableName & " (metid,tooltypesort,tooltypetitle,statusid,statustitle,partnumber,parttitle,mettitle,toolponumber,toolpodate,duedate,lttool,vendortitle)" & vbCrLf & _
             "SELECT t.metid,t.tooltypesort,t.tooltypetitle,t.statusid,t.statustitle,t.partnumber,t.parttitle,t.mettitle,t.toolponumber,t.toolpodate,t.duedate,t.lttool,t.vendortitle" & vbCrLf & _
             "FROM"

  'Call the shared API and return its return code
  RefreshLocalTmpTbl_Project = dbutils_RefreshLocalTmpTbl("clsObjRptToolingDatesByProcessTbl_RefreshLocalTmpTbl", strSQLbe, strSQLfe)
  If RefreshLocalTmpTbl_Project = False Then
    GoTo Exit_RefreshLocalTmpTbl_Project
  End If

Exit_RefreshLocalTmpTbl_Project:
  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing

  Exit Function

Err_RefreshLocalTmpTbl_Project:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: RefreshLocalTmpTbl_Project()")
  RefreshLocalTmpTbl_Project = False
  Resume Exit_RefreshLocalTmpTbl_Project

End Function

 

[sross81]

Thank you for the example. I will look this all over and experiment

 

[sross81]

I am having issues with getting anything in the ADODB collection to work. I think I have a missing reference and when I try to add it I get an error so I think I may need to reinstall Access.

On another note I was able to use my pass through query that executes a stored procedure and set the record source of a report in an MDB applicaiton using this code if anyone wants needs it.

Once I get the references working I am going to try other options. Thank you!

Private Sub Report_Open(Cancel As Integer)
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strEncounterEmailSQLString As String
Dim strProvTbleID As Double


'Set the provtbleID depending on what provider the user selected.
strProvTbleID = [Forms]![frm_SendVarianceNotice]![ProvTbleID]

'Build SQL string to update the pass through query.
strEncounterEmailSQLString = "EXEC sp_GenerateVarianceEmailMainReport " & strProvTbleID

' Set database variable to current database.
Set db = CurrentDb

'Open QueryDef object.
Set qdf = db.QueryDefs("sp_GenerateVarianceEmailMainReport")

'Update the sql pass through query with the new sql string.
qdf.SQL = strEncounterEmailSQLString

'Set the report record source to the results of the stored procedure.
Set rs = db.OpenRecordset("sp_GenerateVarianceEmailMainReport", dbOpenDynaset)
Me.RecordSource = rs.Name
rs.Close

End Sub

 

[mdlueck]

Perhaps try decompiling a COPY of your database file and see if the database troubles get resolved:

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948