Runing parametric queries and/or reports from another database

[Mihail]

Hello !

At this moment I have 3 Front End databases based on a Back End one.
In each Front End I have reports.
Almost all are based on parametric queries.
All parameters are passed using Public Functions.

Here you can see a sketch of how I use this functions:

Public VarName as Long

Public Function RetVal(ParamName As String)
    Select Case ParamName
        Case "frmFormName_ID"
            RetVal = form_FormName.ID
        Case "VarName"
            RetVal = VarName 'Value for VarName is set before running the query
        ......
        Case Else
             Stop 'Debug purpose
    End Select
End Function

In the queries (Criteria Row) I call the function:

RetVal("NameOfParameter")

Almost all the reports (and their queries) are identical.
So, now, I have 3 identical reports (and queries) for each purpose (one in each front end database). Of course I collect parameters from their parent database.

What I wish to do:
To store this reports (and their queries) only once, in a single place (a new front end database or, maybe better, in the back end database) in order to apply it from where I need.
Of course I must be able to pass parameters to their queries.

Is this scenario possible ?
The main question is How to pass parameters from the current database ? (from where I wish to open the reports)

Hope I explain ok the situation.

Thank you in advance !

[sierra7]

Mihail,
I have not followed the intracacies of exactly what you are trying to do but you can't put a Report on the Backend, and another FrontEnd would be viewed as a backend anyway (or maybe I misunderstood)

Anyway, have you considered having a Parameters table? This could be on the Backend (as it just holds data) You can store your values in there and they are available for all users to share.

You can either DLookup() these values when you want them or modify the report's query to join to the parameters table.

Parameters you don't want shared can be identified with a UserID but that is another story!

S7

[Mihail]

Thank you for reply, S7.

Let's see if I understand well what you advice me:
To design a new table (stored in the back end database) and use this table as a "bridge" to temporary store parameter value. Is this what are you suggested ?

If I understand well I think is a very good idea (unless if Access do not provide a mechanism especially for this purpose).

[sierra7]

Yes, that's it!

This is a technique I prefer when you have a SQL Server backend with multi-users. If you want to view say, Sales Orders, and you have 250,000 of them, then native Acess sends all (Most anyway!) of them down the network wire for the FrontEnd to sort out which you need. If instead, you send just the OrderID to the parameters table then run the query on the server you only have one set of Sales Order details sent to view, much quicker.

This code just pokes an order number into the table via a View (an updateable query) on the parameters table.

Application.SetOption "Confirm Action Queries", False
   DoCmd.RunSQL "UPDATE vwParams SET pOrderID =" & lngOrderID & ";"
Application.SetOption "Confirm Action Queries", True

If you need the share the data then you will not need to use a View.
S7

[Mihail]

Cool S7.
As clever as clean.

Thank you again !

[NeoPa]

In some circumstances it may be sensible to store your parameters table in the Front-End. If each user has a separate FE then it ensures no overlap and it ensures such values are evaluated prior to a BE (other than Access) being sent the SQL. Let me stress this is simply an option. There is little to recommend it over the solution already suggested in most circumstances, but if all the options are included there may be situations where this one may be preferred.

[sierra7]

NeoPa,
I think the objective was to allow the parameters to be set by one operative and then used by others (to run reports I understand).

As I know of no way of passing a Global variable from one Front End to another the obvious solution is to poke it into the Back End database so it can be shared.

I admit that I do keep some parameter in the FE e.g. last six customers which display on Tabs, so the user can take phone calls then easily resume to an initial task. Also, some monthly reports take a lot of pre-processing so benefit from keeping the data locally cached after being run once. Line charts, Barcharts and Pivot charts can then use the cache much quicker than re-processing data from the main database. ;-)

Addressing the issue of overlap, the solution is to have one record per UserID, the user then only updates his own record; easily handled by Views in SQL Server, but I digress!

S7

[NeoPa]

S7:
I think the objective was to allow the parameters to be set by one operative and then used by others (to run reports I understand).

You may well be right. I understood the reverse to be true however :-s I make no claim that my understanding was accurate mind you. Mihail does a fine job of communicating in a foreign language, but I nearly always struggle to understand him well.

As for the overlap issue - I don't disagree with you, but from much of the code I've seen posted here (Generally - not related to Mihail in any way) when recordsets are opened there is rarely any consideration given to locking. I know such issues may easily be avoided by those of us that do consider this, but I was thinking of those that don't when I included that option.

Personally, when I had a similar issue to deal with using a SQL Server BE and parameters, I used the approach you suggested and the fact that all was available directly to the SQL Server View made the efficiency of the process go through the roof when compared with storing it locally on the FE. I merely offer the alternative as an option that may be helpful in some cases.

[Mihail]

@NeoP and S7
My circumstances are not as complicated as you think.

Simple, I wish to avoid storing 3 times (in 3 FE) the same query/report.

The best answer to my initial question is what I select.

But now, after I read your last posts, I am in a great doubt if is ok for me to do that.
This because I think that, if I have only one report, this report can't be use by 2 users at the same time. Am I right ?

[NeoPa]

Mihail:
This because I think that, if I have only one report, this report can't be use by 2 users at the same time. Am I right?

No. I don't think you are.

As S7 said earlier (in post #2 which you very sensibly selected as Best Answer.), reports are accessed from the FE only. Reports stored in the BE are inaccessible.

Furthermore, even if multiple users had the same copy of an FE open at the same time, this would not stop them running the same query at the same time. If they each needed different parameters, IE. a different set for each user, then this could still be handled by storing different parameter records for each user.

Does that all make sense?