Using a Pass Thru query as the Record Source for a Sub Report

[sross81]

I have been trying to use a pass through query as the record source for a sub report, but apparently it is not allowed without some work around.

I have been researching online and I came across this article where I can set the record source of the sub form in the main forms on load event:
http://support.microsoft.com/kb/112746

This error will occur in Microsoft Access 7.0 and 97 if you have LinkMasterFields and LinkChildFields defined for the subform or subreport. In order to use an SQL pass-through query as a subform's record source in Microsoft Access 1.x and 2.0, you have to dynamically set the subform's RecordSource property in the main form's Load event. The following is an example of an OnLoad property event procedure in the main form MainForm that dynamically sets the RecordSource property in the subform SPTSubForm:

Sub MainForm_OnLoad Me!SPTSubForm.Form.RecordSource = "[<Name of SQL pass-through query>]" End SubHere is my code. I keep getting a run time error 2467 that the expression you entered refers to an object that is closed or doesn't exist.I have confirmed that the pass through query exists and does work. If I bind it directly to the sub reports on open event and open it on its own it does work.The main report is also bound to a pass through query. The main report has all the summary totals, but the subreport is needed for the detailed data. I can't think of how I could join it together in one query to have it all return and just use the grouping in Access.

Private Sub Report_Load()
Me!AMI10SubReport.Report.RecordSource = "[sp_GenerateVarianceReportData_AMI10]"
End Sub

 

[mdlueck]

I would slightly change the scenario you described, and I think you will have better success.

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

From that post, focus on the solution to download multiple records into an FE temp table.

I would not bind the form directly to the query. Allow the query to run, download the records, and then "disconnect" from the database... where as scrolling through the form would have to leave the connection open to the database for a long LONG time.

Downloading into an FE temp table means you only have one table to bind your form to, where as the Pass-Through query could be joining many tables together in order to run the query.

 

[sross81]

Thank you for the reply. I really would like to not use a local table for this if this is another work around. I have quite a few of them that need to be built. Basically the user opens the report prints it and they are done. It won't leave the connection open long.

If anyone has any other approaches they have taken please share .

 

[mdlueck]

Then perhaps in the same link I provided, use a nested DAO.QueryDef, the inner one to issue the Pass-Through query, the outer one NOT confirued in Pass-Through mode to select from the inner one, and the outer one have be the data source for the form.

Doing so will leave the DB connection open the entire time the form is open. While the query could complete in a blink and disconnect from the SQL BE DB, you will leave the connection open longer bothering the server having to keep track of your connection which really does not need any further information from the server.

 

[DJkarl]

Could you not create a query that is just SELECT * FROM [PassThroughQueryName], then set that as the record source for your report/sub-report?

 

[mdlueck]

Could you not create a query that is just SELECT * FROM [PassThroughQueryName], then set that as the record source for your report/sub-report?

That is what I was describing. That is what I call the outer query which selects from the inner / pass-through query.

 

[sross81]

Thank you both. I did do something just like that. It just took me a little while to make sense of it. This is the first time I have had to work with this scenario.

I have my pass through query that I pass a paramater to here to update that result set (I have the paramter hard coded while I am testing) and then I take the results of the pass through query and input them into a temp table and then the record source of my report is the temp table.

Is there anything else that I could do to this to make it cleaner or more efficient?

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim strSQLString As String
Dim qdf As QueryDef
Dim strSQLMakeTable As String
Dim strFYQtrYr As String
strFYQtrYr = "1-2013"

'Set database variable to current database.
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("sp_GenerateVarianceReportData_AMI10")

'Build SQL string to pass into sp_GetWorklistAMI based on user input parameters.

strSQLString = "EXEC sp_GenerateVarianceReportData_AMI10 " & "'" & strFYQtrYr & "'"

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

DoCmd.SetWarnings False
DoCmd.Close acTable, "VR_AMI10_CurrentQtr"
DoCmd.RunSQL "Delete * from VR_AMI10_CurrentQtr"
'Create a temp table to hold the AMI10 VarianceReport Data.
strSQLMakeTable = "Insert Into VR_AMI10_CurrentQtr (AMI10Variance,AMI10,Name,AMI10Comments,FacilityID,FacilityCode,FYQtrYear,CalMonthYear, AMIEncNbr,DischargeDate) " & _
"Select sp_GenerateVarianceReportData_AMI10.AMI10Variance, sp_GenerateVarianceReportData_AMI10.AMI10, " & _
"sp_GenerateVarianceReportData_AMI10.Name, sp_GenerateVarianceReportData_AMI10.AMI10Comments, " & _
"sp_GenerateVarianceReportData_AMI10.FacilityID, sp_GenerateVarianceReportData_AMI10.FacilityCode, " & _
"sp_GenerateVarianceReportData_AMI10.FYQtrYear, sp_GenerateVarianceReportData_AMI10.CalMonthYear, " & _
"sp_GenerateVarianceReportData_AMI10.AMIEncNbr , sp_GenerateVarianceReportData_AMI10.DischargeDate " & _
"FROM sp_GenerateVarianceReportData_AMI10;"
'Execute the make table statement.
CurrentDb.Execute strSQLMakeTable, dbFailOnError
DoCmd.SetWarnings True
End Sub

 

[mdlueck]

Is there anything else that I could do to this to make it cleaner or more efficient?

Does your code actually work? I do not see you set the Connection string in the DAO.QueryDef that is executing the Stored Procedure. In order to run the DAO.QueryDef as a Pass-Through query, I believe that must be done. So, that looks puzzling to me.

I am accustomed to doing such thusly (though not complete working code)

  Dim daoDB As DAO.Database
  Dim daoQDFbe As DAO.QueryDef
  Dim daoQDFfe As DAO.QueryDef
  Dim strQryNameBE As String
  Dim strQryNameFE As String

  'Attach to the FE DB
  Set daoDB = CurrentDb()

  'Build the BE PT Query
  Set daoQDFbe = daoDB.CreateQueryDef(strQryNameBE)
  With daoQDFbe
[B]    .Connect = ObjAppSettings.ODBCConnectString()[/B]
    .SQL = strSQLbe
    .Close
  End With

  'Build the FE Query
  Set daoQDFfe = daoDB.CreateQueryDef(strQryNameFE)
  With daoQDFfe
    .SQL = strSQLfe
    .Execute
    .Close
  End With

  'Clean up the connection to the database
  Set daoQDFbe = Nothing
  Set daoQDFfe = Nothing
  Set daoDB = Nothing

I also explicitly Close my DAO.QueryDef's as hopeful guarantee that Access would not execute a partially constructed DAO.QueryDef.

 

[sross81]

Yes the code works. I have the conenction string saved in the pass through query. I just pass new parameters here to get a new result set.

 

[mdlueck]

Yes the code works. I have the connection string saved in the pass through query.

Understood. My example code builds brand new DAO.QueryDef objects... thus the difference.

 

[sross81]

I am sure your's is probably the better way to do it . I am just learning as I go. I may switch it around later. I am just getting so close to go live. I can change it in phase 2! Thank you for your help and advice!

 

[mdlueck]

You are very welcome, sross81. I am pleased you have success.

 

[knichols]

Michael,

I'm more of a SQL guy and fairly new to Access and know practically no VBA. I know that this is an old thread, but it seems like you were discussing exactly what I'm needing. I'm working with Access 2010 and wondering if you could break this down for me a little further for me. My situation is probably a bit simpler than the one you lay out above since I don't forsee needing the temp table at the present.

I have a subform that is dependent upon a pass through query that I have created. My main form is tblDashboard and my subform is tblDashboardEmployeeAlerts. I think my primary challenge at the moment is that I am not sure exactly how or where in the application to apply the code sample that you provided.

Let me know if there is any further information that would be helpful

Thanks in advance.

 

[sross81]

knichols,

I put the code behind the click event of the button on the form that the user goes to when choosing their parameters to run the report. Once the click the button the pass thru query takes the parameters and runs it through sql and returns the results into a temp table. The temp table is therefore bound to my report as the record source.

Looking at the way I described the code above I did switch it around since making this post.

Sherri