473,854 Members | 1,821 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Runing parametric queries and/or reports from another database

759 Contributor
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
9 1781
sierra7
446 Recognized Expert Contributor
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 Contributor
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 Recognized Expert Contributor
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 Contributor
Cool S7.
As clever as clean.

Thank you again !
Dec 15 '11 #5
NeoPa
32,584 Recognized Expert Moderator MVP
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 Recognized Expert Contributor
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,584 Recognized Expert Moderator MVP
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 Contributor
@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,584 Recognized Expert Moderator MVP
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
2238
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 objects. That's not really that clear, so here's an example: If I have a table named "table_i_would_like_to_delete_but_don't_know_if_any_object_is_using_it",
2
8424
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 "allow" c) to link 5 Tables in the PMF Database ... Table1,2,3,4,5 Next ... I need to set up some forms & queries in my Temp database which will access data from the PMF database. Do I need to create any
2
1854
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 switchboard and try to open a table, query or report, the tabs show up for table, query, report and etc. However, the white box that lists the names of the tables, queries, reports does not appear. A small rectangle appears in the upper left hand corner of...
3
7241
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 database. The tables in the second database would have the same names as those in the first database. Can this be done and if so how? dixie
3
3334
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 the backend or even in a second backend database. I that case I can easily spread new reportslayouts to my customers, without rolling out an update of my frontend database. But: is that possible in MSACCESS 2003 and how? Ca it be done with a...
1
1537
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 up to my home office Master database... any ideas? Thanks!!!! Dav
0
1268
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 also have the ObjectID which is in the other database MSysObjects (in case it is needed). Case 1 is for tables and case 5 is queries. The Set OtherTableDef and Set OtherQueryDef work OK, but not the OpenTable Error is "Microsoft Acces can't...
6
5895
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 by putting it in its own database. If the sensitive data is in a database called d_SensitiveData, and in that database there is a table called 't_A' (I know, not very informative, but this is only a trivially simple example :-), and I have a...
3
1502
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? I'm using Access 2002, 2003, Windows XP and Yahoo email. How can I send these queries and reports without sending the entire database each time? Thank you for your assistance! Mark / SF Bay Area
7
16417
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 stored procedure needs to reside on my sql 2005 server. The task is very simple in Access as we have ODBC connections set up to the UniData via Informix (or IBM) UniData ODBC drivers. I can easily combine my UniData and Sql Server tables from...
0
9902
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10764
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10371
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7083
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5753
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5945
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4565
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4165
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.