X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Report Printing A Report That Relies On A Parameterized Query, Access 2010    
post Jun 25 2019, 11:05 AM

Posts: 2
Joined: 19-June 19

Forgive me if I make mistakes on this question - this is my first attempt at submitting a question.

I have a multi-user (~15 people) solution where Access is used as a front-end read-only monitoring and reporting tool for a SQL Server database.
Several of the interfaces involved in this solution involve using queries that are built on the fly to filter results from SQL views.
These filters are absolutely necessary to execute at the server side due to the large volume of data involved.
In other words, to pull the results of an unfiltered query from some of the SQL server views will NOT be efficient both from a bandwidth standpoint and user interface response standpoint.

The approach I am currently using:
The solution I developed to alleviate bandwidth and presentation logjams is to use pass-through queries as the way to store the SQL query code and then copy and modify the SQL code to use appropriate filter parameters prior to ADODB execution.
Since these SQL requests are against views, much of the business logic is baked into the view. The only filters needed at this point would be name or identity value(s) of one form or another.
The resulting recordset is almost always relatively small (manageable and responsive). Once I have the recordset object in VBA memory, I can populate form objects like textboxes and listboxes with relative efficiency.
This actually works exceedingly well for form presentations and interactions. Relatively low network traffic, fast UI response and data display are the end results because Access only gets the necessary data needed from SQL Server during user interactions.
This also keeps the Access file itself relatively small because it does not ingest large amounts of data locally to the Access file.

Example code follows (consider this pseudo code - may need tweaking to make it work if you copy it verbatim).

Dim SQLConn As New ADODB.Connection
Dim Rset1 As New ADODB.Recordset
Dim ConnString As String
Dim mySQL As String
Dim DBS As Database
Dim TemplateQDef1 As QueryDef

'Get the SQL code from the pass through query object
Set DBS = CurrentDb
TemplateQDef1 = DBS.QueryDefs("PT_TemplateSQL_UseCaseABC") '-- this is a pass through query object in the Access database file

'Apply parameter values within the SQL using text replacement where a given value selected in the form is used as the parameter value.
mySQL =  Replace(TemplateQDef1.SQL,"xxxxxxx",Trim(Me.NameFilterTextBox.Value)) '-- this is a textbox on the form where a user enters the partial name of a person they want to review.   The SQL code already has the necessary like clause set up with '%xxxxxxx%'.

' Connection to SQL via ODBC
ConnString = "DSN=OurSQLDatabase_Prod;"
SQLConn.Open ConnString

' Create recordset object using the modified SQL code.
Rset1.CursorLocation = adUseClient
Rset1.Open mySQL, SQLConn, adOpenStatic, adLockBatchOptimistic, adCmdText

' From this point on, I can populate relevant form objects using ADODB recordset properties and methods for presentation purposes.
' Simplified Example - other more complex presentation can be accomplished if necessary
Set Me.ResultsList.Recordset = UserRset1

' Once data is populated on the form, I close up the recordset and connection objects as a good practice.
Set SQLConn = Nothing

So... the question I have...
Can this technique be used to populate a report for printing?
From what I have experienced, it is not possible to modify the recordset values of unbound field objects upon requesting a print preview in a report.
In other words, the only working method I have seen for reports is to dump the recordset values to a temporary table in the local database file, and then call up the print preview of the report wherein the report uses the temporary table as a recordset.

Where this becomes a problem is in a multiuser environment where several people are using the same Access file at the same time.
I could create a temporary table to hold the data, but if more than one person tries to run the same report using different data, I feel we would run into concurrency challenges.

Ideas I have off the cuff:
- Create temporary tables with names that integrate the login name of the user so that only they would interact with the table. The challenge here is to modify/update the recordset for the report prior each print preview call. This could still result in concurrency issues unless I make separate copies of the report object for each person. This gets complicated.
- Create a temporary table for the report, but have a field on the report that identifies the login for the current user (basically a session indicator for each record in the temporary table). Then I could use a query to filter the temp table on the login value of the current user and use the query as a recordset value for the report I am going to try this idea. It will take some work, but I feel it is more scalable for a multi-user environment. We will see.

Any other ideas, thoughts? I will post anything I discover as I scratch at this puzzle.
Thank you in advance for all of your help, patience, and consideration.

- Al
Go to the top of the page
post Jun 25 2019, 11:09 AM

Access Wiki and Forums Moderator
Posts: 75,722
Joined: 19-June 07
From: SunnySandyEggo

Hi Al,

Welcome to UtterAccess!

When you talk about creating a temporary table, you are talking about a local table in the Access file, correct? If so, I don't see how you could have interference between your users, unless they's all sharing the same front end file. Are they?

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Jun 25 2019, 11:28 AM

Posts: 2,461
Joined: 4-February 07
From: USA, Florida, Delray Beach

Where this becomes a problem is in a multiuser environment where several people are using the same Access file at the same time.

To me this appears to be a scenario where Multiple Users are utilizing the same Access FE? If this is the case, IMHO, this is not a very good design at all. I'm with theDBguy on this one. Each User should have their localized FE pointing to the SQL Server BE. Creating a TEMP Table based on each User's Name should be an easy task with the uniqueness of the Table guaranteed. I see no concurrency issues and filtering the Table based on the User Name would be easy.
This post has been edited by ADezii: Jun 25 2019, 11:29 AM
Go to the top of the page
post Jun 26 2019, 12:54 PM

Posts: 2
Joined: 19-June 19

Yes... they are sharing the same front-end file.
The file contained no hard tables itself. Only forms, queries, and reports.
No data entry functionality!! This is also another factor that keeps this thing alive. The functionality only involves searching and reporting on data.

It may be low rent, but it existed before my time and this office is NOT open to developing a modernized client.

This approach actually does work very well.
I got involved because they they wanted some improvements to some of the reporting/lookup tools that are in this thing.
As for everyone getting their own FE copy... again.... not something that the management here is willing to entertain at all. I don't run the environments here, I just help maintain the systems.
To be honest, it is easier to maintain one FE file than to maintain a copy on every endpoint.
Other benefits apparently include COOP support. We had a building disaster recently and this was one of the few gems that was working the moment everyone started getting back to work at our alternate site.

A follow-up to what I discussed originally:
I have completed a prototype where I created a "temp" table in the Access file that is populated with the records for a report with tags identifying which user the records are for.
A better name may be a "reporting" table as it's only purpose is to hold data that is to be reported for a very brief period of time. This is a table object in the FE file that is established for the report to point at, but where the content changes as necessary for each report generation.
Queries pull the data for the particular user at the moment the report is generated. Data for the particular user is cleaned out of the table after all reporting is completed/generated.
I have a few testers running it through it's paces, but so far everyone is reporting good results and are very please. I am also monitoring the file to see where it may be necessary to schedule any shrinking practices with the file.
We will see if this hold water for these folks over time. I am still working on convincing them to spend money to move this solution to a cloud platform. Only time will tell.

Thank you for your inputs.

- Al

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    18th July 2019 - 10:54 PM