John W. Colby
jwcolby at colbyconsulting.com
Fri Mar 11 05:50:57 CST 2005
BTW, you can create base queries, one for each table, which simply do a SELECT * FROM TBLX. Then open the properties for the query and make it a snapshot query. Now have them use those base queries instead of using the table directly. Because the base queries are snapshot queries, no data can be changed. If they go around those queries to use the tables all bets are off. You could of course use Access security to give them access to the queries but not the tables. John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Sad Der Sent: Friday, March 11, 2005 1:53 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Automating parameterized action queries..... You both have good points. I explained this to them and they understand the risks...if they do anything with it.... However, because the risk of users trashing the DB is so high this is a project in wich I do not take any responsible what so ever when anything crashes, data is incorrect etc. To avoid as much problems as possible in the future I created a lot of logging tables etc so I can see what tables were linked and wich queries run to create a certain Excel report. How many rows the report has etc. So, thnx a lot for the answers and discussion but now it's time to do some programming. ~Sander --- "John W. Colby" <jwcolby at colbyconsulting.com> wrote: > When I have users that need to run their own queries > I set them up with a > local (to their machine) front end, linked to all > the necessary tables. > They can then build queries and reports to their > heart's content. It is > important to know that they know what they are doing > because they are > "directly in the tables" with queries and can trash > the db pretty easily. > > John W. Colby > www.ColbyConsulting.com > > Contribute your unused CPU cycles to a good cause: > http://folding.stanford.edu/ > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On > Behalf Of Rocky Smolin - > Beach Access Software > Sent: Wednesday, March 09, 2005 10:20 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Automating parameterized > action queries..... > > > Makes supporting a database pretty exciting, then, > doesn't it? When it > breaks, you just never know what you're going to > find. > > So if they can handle modifying their own queries, > can't they fill in the > blank in the parameter prompt? > > Rocky > > ----- Original Message ----- > From: "Sad Der" <accessd666 at yahoo.com> > To: "Access Developers discussion and problem > solving" > <accessd at databaseadvisors.com> > Sent: Wednesday, March 09, 2005 1:43 AM > Subject: Re: [AccessD] Automating parameterized > action queries..... > > > >I completely agree user should not mess around in > the database. > >However this is a very clear demand. Users must > have access to the > >queries to alter them at any time!! > > > > Thnx anyway. > > --- Rocky Smolin - Beach Access Software <bchacc at san.rr.com> wrote: > > > >> Sander: > >> > >> I always take parameters from a form. Don't like > to > >> let the users muck > >> around in the database container. So get the > >> parameter into the query I > >> make a > >> > >> Public Function GetParameterX > >> > >> > > > GetParameterX=Forms!frmWhatever!txtBoxHoldingParameter > >> End Function > >> > >> Then in the query under criteria I put > >> GetParameterX() and it fetches the > >> contents of the text box from the form when it > runs. > >> > >> Probably not the most elegant solution but it > works > >> and its easy. But you > >> have to run the query from the form. > >> > >> The users I write for prefer it. Almost none of > >> them would know how to run > >> a query from the database container and most > don't > >> even know what a query > >> is. > >> > >> HTH > >> > >> Rocky Smolin > >> Beach Access Software > >> http://www.e-z-mrp.com > >> 858-259-4334 > >> > >> ----- Original Message ----- > >> From: "Sad Der" <accessd666 at yahoo.com> > >> To: "Access Developers discussion and problem > >> solving" > >> <accessd at databaseadvisors.com> > >> Sent: Tuesday, March 08, 2005 6:33 AM > >> Subject: RE: [AccessD] Automating parameterized > >> action queries..... > >> > >> > >> >I also thought of that but I hoped their was a > way > >> to > >> > fill the parameters per query with form input. > >> > > >> > That way i could make a form for the users in > wich > >> > they can enter: > >> > - a new report > >> > - queries per report > >> > - parameters per query > >> > - parametertype per parameter (eg date, number, > >> etc) > >> > > >> > With this info I could, based on a report > >> selection > >> > build a form dynamicly. > >> > > >> > Well I have to look at this some more and let > the > >> > customer decide. > >> > > >> > Regards, > >> > > >> > Sander > >> > > >> > --- Bobby Heid <bheid at appdevgrp.com> wrote: > >> >> Why not have two sets of queries, one set that > >> looks > >> >> to a form (this one is > >> >> called programmatically), and one set that > >> queries > >> >> the user for the > >> >> parameters? > >> >> > >> >> Bobby > >> >> > >> >> -----Original Message----- > >> >> From: accessd-bounces at databaseadvisors.com > >> >> [mailto:accessd-bounces at databaseadvisors.com] > On > >> >> Behalf Of Sad Der > >> >> Sent: Tuesday, March 08, 2005 8:10 AM > >> >> To: Access Developers discussion and problem > >> solving > >> >> Subject: RE: [AccessD] Automating > parameterized > >> >> action queries..... > >> >> > >> >> > >> >> You are correct. However the queries are not > >> >> executed > >> >> via from several forms but they are executed: > >> >> 1- via one form > >> >> 2- a user manually clicks it in the database > >> >> container. > >> >> > >> >> The process is as follows: > >> >> 1 - the reports (xls files) are stored in the > >> >> database > >> >> 2 - the queries that create the report are > also > >> >> stored > >> >> in the database > >> >> 3 - the user selects a report > >> >> 4 - the necessary queries are executed one by > one > >> >> > >> >> However, several times a week a manager > requests > >> a > >> >> part of the report that is slightly different > >> from > >> >> the original. A user then > >> >> must have the change to quickly adjust the > query > >> and > >> >> run it manually. > >> >> > >> >> HTH > >> >> > >> >> Sander > >> >> > >> >> > >> >> --- Bobby Heid <bheid at appdevgrp.com> wrote: > >> >> > If you are not wanting to encode a > particular > >> form > >> >> > field because the query > >> >> > is called from several forms, might I > suggest > >> that > === message truncated === __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com