Pass thru query w/params as report recordsource not working

[Glenley]

I have a module that returns a bulk set of reports with subreports from SQL statements. I recently upgraded to MSSQL for my backend. So far I love MSSQL but it's super slow when running complex queries so I'm trying to feed these reports from pass thru queries. The sub ran fine before but as a pass thru I get an empty report with "Name#" in the fields. Each one of these SQL statements run fine as individual Pass Thru queries with small modifications to the WHERE statements, just not as report record source. Ill post the code below. What am I doing wrong here? TIA

Public Sub PTWeeklyReports(S As Long)


    Dim SDate As Date, EDate As Date
    Dim WAppS As String, ChemS As String, TimeS As String
    Dim db As Database, qd1 As QueryDef, qd2 As QueryDef, qd3 As QueryDef


    SDate = InputBox("Start Date", "Enter Date")
    EDate = InputBox("End Date", "Enter Date", Date)


    'Weekly Application Reports by date
    WAppS = "SELECT OperationsT.OperationsID, OperationsT.OperationsDate, TruckT.TruckNumber, ContractingCompanyT.ContractingCompany, " & _
                "OperationsT.City + ', ' + OperationsT.State AS CityState, " & _
                "SupervisorT.FirstName + ' ' + SupervisorT.LastName AS Supervisor, " & _
                "EmployeeT.ApplicatorNumber, OperationsT.TailgateDiscussion, OperationsT.AMWaterUsage, " & _
                "OperationsT.PMWaterUsage, OperationsT.Footage, OperationsT.Width, SubstationT.SubStation, " & _
                "OperationsT.LocationStart, OperationsT.LocationEnd, OperationsT.NatureOfBreakdown, " & _
                "OperationsT.Comments, SubstationT.County, OperationsT.IsBilled, OperationsT.TotalUsage, " & _
                "OperationsT.AMTime, OperationsT.PMTime, OperationsT.AMWind, OperationsT.PMWind, OperationsT.AMSpeed, " & _
                "OperationsT.PMSpeed, OperationsT.AMTemperature, OperationsT.PMTemperature, OperationsT.AMGroundConditions, " & _
                "OperationsT.PMGroundConditions, OperationsT.TimeStart, OperationsT.TimeStop, OperationsT.AMTravel, " & _
                "OperationsT.PMTravel, OperationsT.TotalTime, OperationsT.OverTime, OperationsT.AcresSprayed, " & _
                "OperationsT.GallonsPerAcre , EmployeeXOperationsT.PrimaryApplicator " & _
            "FROM TruckT RIGHT JOIN SubstationT RIGHT JOIN ContractingCompanyT RIGHT JOIN OperationsT ON " & _
                "ContractingCompanyT.ContractingCompanyID = OperationsT.ContractingCompanyID " & _
                "ON SubstationT.SubstationID = OperationsT.SubstationID ON TruckT.TruckID = OperationsT.TruckID " & _
                "LEFT JOIN SupervisorT ON OperationsT.SupervisorID = SupervisorT.SupervisorID " & _
                "INNER JOIN EmployeeXOperationsT ON OperationsT.OperationsID = EmployeeXOperationsT.OperationsID " & _
                "INNER JOIN EmployeeT ON EmployeeXOperationsT.EmployeeID = EmployeeT.EmployeeID " & _
            "WHERE OperationsT.OperationsDate Between '" & SDate & "' And '" & EDate & "' " & _
                "AND OperationsT.ContractingCompanyID=" & S & " " & _
                "AND EmployeeXOperationsT.PrimaryApplicator=1 " & _
            "ORDER BY OperationsT.OperationsDate ASC;"


    'Chemical Subreport
    ChemS = "SELECT OperationsT.OperationsID, ChemicalT.ChemicalName, ChemicalT.EPARegistration, ChemicalT.LotNumber, " & _
                    "ChemicalT.BatchNumber, ChemicalT.ActivePctPer100, CONVERT(varchar,Round(AMWaterUsage+PMWaterUsage*ActivePCTPer100*128,1)) & ' oz' " & _
                    "AS TotalChemUsage, OperationsT.AMWaterUsage, OperationsT.PMWaterUsage " & _
                "FROM ChemicalT INNER JOIN RecipeT INNER JOIN RecipeXChemicalT ON " & _
                    "RecipeT.RecipeID = RecipeXChemicalT.RecipeID ON ChemicalT.ChemicalID = RecipeXChemicalT.ChemicalID " & _
                    "INNER JOIN OperationsT ON RecipeT.RecipeID = OperationsT.RecipeID " & _
                "WHERE OperationsT.OperationsID=Reports!WeeklyApplicationR!OperationsID;"


    'Employee Time Subreport
    TimeS = "SELECT OperationsT.OperationsID, EmployeeT.FirstName & ' ' & EmployeeT.LastName " & _
                    "AS EmployeeName, EmployeeXOperationsT.PrimaryApplicator, EmployeeT.Wage, OperationsT.TimeStart, " & _
                    "OperationsT.TimeStop, OperationsT.AMTravel, OperationsT.PMTravel, OperationsT.TotalTime, OperationsT.Overtime " & _
                "FROM OperationsT INNER JOIN EmployeeT INNER JOIN EmployeeXOperationsT ON " & _
                    "EmployeeT.EmployeeID = EmployeeXOperationsT.EmployeeID ON OperationsT.OperationsID = EmployeeXOperationsT.OperationsID " & _
                "WHERE OperationsT.OperationsID=Reports!WeeklyApplicationR!OperationsID;"


    On Error GoTo ErrMsg
    Set db = CurrentDb
    Set qd1 = db.QueryDefs("PTApplicationQ")
    qd1.Connect = ConString
    qd1.SQL = WAppS
    Set qd2 = db.QueryDefs("PTChemicalQ")
    qd2.Connect = ConString
    qd2.SQL = ChemS
    Set qd3 = db.QueryDefs("PTTimeQ")
    qd3.Connect = ConString
    qd3.SQL = TimeS


    With DoCmd
        .Echo False
        .OpenReport "WeeklyApplicationR", acViewReport
        Reports!WeeklyApplicationR.Report.RecordSource = "PTApplicationQ"
        Reports!WeeklyApplicationR!WeeklyChemicalR.Report.RecordSource = "PTChemicalQ"
        Reports!WeeklyApplicationR!WeeklyTimeR.Report.RecordSource = "PTTimeQ"
        .OpenReport "WeeklyApplicationR", acViewPreview
        .Echo True
    End With


KillSub:
    
    qd1.Close
    Set qd1 = Nothing
    qd2.Close
    Set qd2 = Nothing
    qd3.Close
    Set qd3 = Nothing
    Exit Sub
    
ErrMsg:
    
    ErrorMsg 'global error message sub to reduce keystrokes
    Resume KillSub
    
End Sub

[madpiet]

One option is to basically push all the processing to SQL Server. Create a stored procedure that receives the parameter values, and then returns the results of a query. one thing you absolutely need to do with SQL Server is index the join columns or performance is going to make you crazy.

[Minty]

Unless I'm missing something, you won't be able to pass through a query with

Reports!WeeklyApplicationR!OperationsID

in it as criteria, as MySQL will have no knowledge of the Reports object.

Also, you may as well remove the order by clause, as it's completely ignored by Access reports, it has it own sorting and grouping.

[Glenley]

Ahh okay. I guess I still have a lot to learn about MSSQL. I suppose I could use recordsets to create local Temp Tables to feed the reports until I understand it better. Just out of curiosity, how would one go about referencing the OperationsID to feed the subreport when using pass through query, keeping in mind that it has to requery for each individual report in the batch? Or am I thinking about this all wrong? I'm moving from SharePoint as a backend. I know MSSQL is infinitely more powerful than SP but when running complex queries from the Access front end like this, SP is about 300% faster. So I'm having to redesign large parts of my DB to improve performance. Any other words of wisdom would be greatly appreciated

[Minty]

The sub-reports should be linked on a Master/Child relationship, so you don't need to filter them, just create a query on the server that presents all the data you need for the report.
You could use a pass-through to limit the data to just the relevant overall master records, and use that as the sub-reports data source.

Does that make sense?

[Glenley]

Minty, not 100% clear but that's through no fault of yours. I need to just sit down and learn the finer points of MSSQL and how to best use them with Access. I know there's a lot of educational resources available. I do thank you for the input, but I certainly don't expect anyone to give me a course in SQL Server on this forum. Until I learn how to best utilize the backend, I'm going to create local temp tables to feed the reports

[Minty]

No Problem, the point of a forum is to pick peoples brains.
With a SQL server back end (Which I use extensively for 90 % of our clients), you sometimes need to optimise for the way Access works with the Back end, and try and minimise network traffic.

In your case your sub reports (just like a sub-form) will have a matching Master ID FK in them to associate them with your header records.
You could use a pass through to only return those records and use that as the sub-report recordsource.
You could build that dynamically to use in a generic PT query, something like :

StrSQL = "SELECT Myfields1, Myfields2, Myfields3, etc, etc
StrSQL = StrSQL  &  " FROM  MyTable WHERE MyTable.ForiegnKeyField = " & Me.MyIDUsedtoGenerateMyReportFromMyForm

MySubReportQDF.SQL = strsql

You get the drift I hope?

[Glenley]

Okay yes, I see what you're saying. I thought that's what I was doing in my original code. It was working fine until I tried to make it a Pass Thru. Obviously there were some syntax changes that had to be made and I also changed the WHERE clause to

"WHERE OperationsT.OperationsID=" & Reports!WeeklyApplicationR!OperationsID & ";"

But I'm still getting a blank report when I run it. The master report will have Name# in the fields and the subreports are basically invisible although the visible property is set to true. If I run the SQL for the master report as a pt query, it returns all the right records but when I try to populate a report with them is where the trouble happens. The same with the subreport SQL statements. I can run them as standalone pt queries and manually insert the OperationsID, they return correct info. It's only when opening the report

[June7]

When you say MSSQL, do you mean MS SQLServer?

Did you make sure the passthrough query object SQL was actually modified with the parameter value?

Should possibly reference a form control for the input.

[Glenley]

Yes, I mean SQL Server. And yes, the sql statements are running with the correct param values when run them as standalone pt queries. The "S" input in my original code is referencing a combo box on my main menu with contracting companies we work for. I've been using this subroutine for the last two years with no issues when I was using SharePoint. The problems started when I moved to SQL Server and had to start using pass thru queries to speed things up. My previous code configurations were extremely slow. I'll admit I'm an absolute beginner with SQL server and all of it's intricacies but I don't think the problem is is my SQL since the queries run fine. I tend to think it has something to do with how I'm setting the recordsource for the reports but I could be wrong

[Glenley]

Okay, correction. The problem is in my SQL. The issue is the reference to Reports!WeeklyApplicationR!OperationsID in the master report. I just ran it without the subreports and it ran fine. Now I just need to figure out how to reference the OpsID in the master report for the subreports

[June7]

You want to modify RecordSource of a subreport? Why? How is subreport linked to main report? Master/Child property setting not adequate to define subreport dataset?

[Glenley]

I want to set the subreport record source with a pt query. I have to set it in VBA for the main report because I'm passing parameters so I went ahead and did so for the entire report. NO, master/child property is not doing it.

Now it's saying:

"You can't use a pass-thru query or a non fixed column crosstab query as a record source for a subform or subreport"
"Before you bind the subform or subreport to crosstab query, set the query's ColumnHeadings property"

[June7]

I have no problem using PT query as RecordSource for a subform. Haven't tried with subreport but expect it would work as well.

[Minty]

Create a local crosstab query based on the pass through.
Or if you have fixed column headings get a bit clever and create the crosstab result in MySQL?