By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,470 Members | 1,530 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,470 IT Pros & Developers. It's quick & easy.

Runing parametric queries and/or reports from another database

100+
P: 759
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:
Expand|Select|Wrap|Line Numbers
  1. Public VarName as Long
  2.  
  3. Public Function RetVal(ParamName As String)
  4.     Select Case ParamName
  5.         Case "frmFormName_ID"
  6.             RetVal = form_FormName.ID
  7.         Case "VarName"
  8.             RetVal = VarName 'Value for VarName is set before running the query
  9.         ......
  10.         Case Else
  11.              Stop 'Debug purpose
  12.     End Select
  13. End Function


In the queries (Criteria Row) I call the function:
Expand|Select|Wrap|Line Numbers
  1. 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 !
Dec 15 '11 #1

✓ answered by 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

Share this Question
Share on Google+
9 Replies


Expert 100+
P: 446
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
Dec 15 '11 #2

100+
P: 759
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).
Dec 15 '11 #3

Expert 100+
P: 446
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.
Expand|Select|Wrap|Line Numbers
  1. Application.SetOption "Confirm Action Queries", False
  2.    DoCmd.RunSQL "UPDATE vwParams SET pOrderID =" & lngOrderID & ";"
  3. Application.SetOption "Confirm Action Queries", True
If you need the share the data then you will not need to use a View.
S7
Dec 15 '11 #4

100+
P: 759
Cool S7.
As clever as clean.

Thank you again !
Dec 15 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
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.
Dec 15 '11 #6

Expert 100+
P: 446
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
Dec 15 '11 #7

NeoPa
Expert Mod 15k+
P: 31,709
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.
Dec 15 '11 #8

100+
P: 759
@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 ?
Dec 15 '11 #9

NeoPa
Expert Mod 15k+
P: 31,709
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?
Dec 15 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.