473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Runing parametric queries and/or reports from another database

759 512MB
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

9 1758
sierra7
446 Expert 256MB
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
Mihail
759 512MB
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
sierra7
446 Expert 256MB
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
Mihail
759 512MB
Cool S7.
As clever as clean.

Thank you again !
Dec 15 '11 #5
NeoPa
32,556 Expert Mod 16PB
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
sierra7
446 Expert 256MB
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
32,556 Expert Mod 16PB
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
Mihail
759 512MB
@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
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: Jonathan LaRosa | last post by:
Hi all - I'm wondering if anyone has (or knows of) a tool that will allow me to search through VB code, tables, queries, reports, forms, and other objects, for references to all other types of...
2
by: Prakash Wadhwani | last post by:
I have a database called "TEMP" On Opening the "TEMP" Database, I need to use VBA : a) to connect to another database called "PMF" b) the PMF database requires a password on opening called...
2
by: Eric | last post by:
I have an Access 95 database. This database has run for years and now is giving me a problem. When opened, the switchboard works fine and the database functions. However, when I close the...
3
by: Dixie | last post by:
I know how to append records from one table to another in the same database, but I need to be able to append the records from all the tables in one database into new empty tables in another...
3
by: Bob | last post by:
For maintenance reasons I would like to split my DB in an Frontend and a Backend. The frontend is an MDE. The backend contains all the tables. Nothing special. But I will also have my reports in...
1
by: Parasyke | last post by:
How can I, without using Replication technology, append a table in my master database from data in another database? I have several field offices with sales data that I want, on demand, to append...
0
by: Phil Stanton | last post by:
I am trying to view the design of tables and queries in another database. This is the code on a form, "ObjectName.Column(0))" is the name of a table or query selected from the other database. I...
6
by: Ted | last post by:
I am construvcting a number of databases, some of which contain sensitive data and most of which do not. I am attempting to handle the security issues involved in protecting sensitive data in part...
3
by: markxxiv | last post by:
How can I send queries, reports, and perhaps forms for one database via email to a colleague who also has Access 2003 and the same database as the one I'm using to create these queries and reports? ...
7
by: mkarbarz | last post by:
Hello, I need to come up with a stored procedure that will allow me to read data from another database. The database I need to read the data from is a UniData residing on a Unix server. The...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.