|
security
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Queries being modifiedI have a bunch of queries that need to remain static for functionality, and a couple of users who are constantly modifying them and subsequently breaking reports. Using any kind of logon security is not an option, as per management. I can put the queries into functions and thus hide them via MDE, but there are several dozen to be converted. Is there some way to hide these queries when creating the MDE? If not, does anyone know of a tool to either convert to code (like you can a macro) or a way to output the SQL statements to a single text file to simplify the process of removing line breaks? Doing this manually is taking forever, and management wants it done yesterday (as usual). TIA, Susan "SusanV" <svanallen@nospam-mvps.org> wrote in message Doesn't management want to do anything about the medling users?news:uIyQgHSOFHA.2728@TK2MSFTNGP15.phx.gbl... > Hi all, > > I have a bunch of queries that need to remain static for functionality, > and a couple of users who are constantly modifying them and subsequently > breaking reports. Using any kind of logon security is not an option, as > per management. I can put the queries into functions and thus hide them > via MDE, but there are several dozen to be converted. You could use the SQL statement of your queries as the recordsource for your report, rather than saved queries. Then once it's a MDE they can't modify the report. Be sure to keep the original MDB if you do this. There are a number of things you can do to 'hide' things from the users. Backup your database; you can easily lock yourself out playing around with these features. Create custom menus/toolbars for use throughout your application. Create a startup form (a main menu form if you have one) that is opened on startup. Use the features in Tools, Startup to set the startup form set your default menu (the custom one you made) disable all the checkboxes about allowing built in menus, toolbars, changes etc. hide the db window (ensure the custom menu you create does not include the Windows, Unhide item) Click on the Advanced button and uncheck the allow special keys (this will disable the F11 key, among others) You can hold down the shift key wh you open the database, to bypass these settings. If your users may know about the shiftkey bypass, you can disable the shift key bypass - there's an example in help for doing this (look for AllowBypassKey) or at http://www.mvps.org/access/modules/mdl0011.htm and http://www.mvps.org/access/general/gen0040.htm You can also hide the share where the database is located, so that users can't easily find it (if they know the path though, they can get to it). \\server\share$ rather than \\server\share -- Joan Wild Microsoft Access MVP Thanks Joan,
I've been trying to convince them, but they won't go for logons - "Too many passwords and logons already" (this is an internal database for tracking various projects we do for the military, so you can imagine the number of user/passes people have to remember to do their regular jobs on those projects, never mind this tracking database.) Bah. The queries that were creating the biggest problem were update queries and I've put those into a function, getting rid of the both the macro and the 12 queries it called, so the worst is past me, but there's still a lot of things to be done. Some queries still have to be hidden but for now at least I've got a bit of a handle on it. I already hide the database window on startup but as you said F11 brings it back and the more savvy users know this. I need to split the database, so that when I work on a backup copy I don't have to worry about data-loss. As to the backups, I ONLY modify a copy, never the live version, then if it's all good I rename the old, back THAT up and then pop in the modified copy. I hate having to go to tape unless there is absolutely no choice. Really gotta split this database soon. I'm working on a custom startup form, as the switchboard is far too limited (this project never stops growing) but that is far from complete. Ever time I get back to that something else comes up - like last week they completely changed the reporting requirements for the third time since Christmas. Gotta love the military! This project was started by someone else, several years ago, and was never meant to grow to the point it has. Now that person is gone, and I'm learning by doing, so it's difficult to cover all the bases. Just getting it normalized with the proper relationships was quite the learning curve - I was hired as an Exchange Admin, and now do literally everything, including, apparently, writing code. Thankfully I've got lots of reference books, and some VB, but sometimes I just can't find what I need and you guys are great to be here helping! Thanks again for your help, Susan Show quote "Joan Wild" <jwild@nospamtyenet.com> wrote in message news:O0TL6LSOFHA.244@TK2MSFTNGP12.phx.gbl... > > "SusanV" <svanallen@nospam-mvps.org> wrote in message > news:uIyQgHSOFHA.2728@TK2MSFTNGP15.phx.gbl... >> Hi all, >> >> I have a bunch of queries that need to remain static for functionality, >> and a couple of users who are constantly modifying them and subsequently >> breaking reports. Using any kind of logon security is not an option, as >> per management. I can put the queries into functions and thus hide them >> via MDE, but there are several dozen to be converted. > > Doesn't management want to do anything about the medling users? > > You could use the SQL statement of your queries as the recordsource for > your report, rather than saved queries. Then once it's a MDE they can't > modify the report. Be sure to keep the original MDB if you do this. > > There are a number of things you can do to 'hide' things from the users. > > Backup your database; you can easily lock yourself out playing around with > these features. > > Create custom menus/toolbars for use throughout your application. > Create a startup form (a main menu form if you have one) that is opened on > startup. > Use the features in Tools, Startup to > set the startup form > set your default menu (the custom one you made) > disable all the checkboxes about allowing built in menus, toolbars, > changes etc. > hide the db window (ensure the custom menu you create does not > include the Windows, Unhide item) > Click on the Advanced button and uncheck the allow special keys > (this will disable the F11 key, among others) > > You can hold down the shift key wh you open the database, to bypass these > settings. If your users may know about the shiftkey bypass, you can > disable > the shift key bypass - there's an example in help for doing this (look for > AllowBypassKey) or at > http://www.mvps.org/access/modules/mdl0011.htm > and > http://www.mvps.org/access/general/gen0040.htm > > You can also hide the share where the database is located, so that users > can't easily find it (if they know the path though, they can get to it). > \\server\share$ rather than \\server\share > > > -- > Joan Wild > Microsoft Access MVP > It seems to me that this is a business process problem - not a
technical one. >From management's viewpoint, who are the authorized programmers for this particular database?- If is is YOU, then no-one else should alter it. If they do, you do not try to fix it: you complain to management. Just as if someone has forced the door to your locker. You do not get out your toolkit and fix it: you report the matter to management. - If it is NOT you, ie. management believes that SEVERAL people (including you) can change the code, then, you need some kind of agreement, or sourcecode control system, to stop evertyone's changes from conflicting. Clearly, the other people do not agree that you are the only one with the authority to change the database code. If so, adding passwords etc. will not solve the problem. For example, they might just over-write your password-protected database with an earlier, non password-protected version that they kept for that purpose! So as I said initially, this is a business process problem - not a technical one, IMO. HTH, TC It's not a matter of people "taking over" the project, or anything
malicious - it's more that people use existing queries to get different info by changing parameters, then when they close the query they altered they aren't thinking and click ok to save the changes. Not intentional, just an oops. No one changes any modules or table design or anything like that, just the queries. We've had meetings with the people who do this (there are only 2 with a clue, so it's easy to pin down) and they say they'll be more careful, but then it happens again. So now I'm hiding the most vital queries in functions using DoCmd.RunSQL. As to them over-writing, the db gets backed up every night to 3 different locations, 2 of which require Domain Admin rights to even list folder contents. (I'm the Net Admin who is also now the DBA) If this was a more formal company a complaint to management would be the way to go, but it's a more "team-oriented" place with a small project group, so I've learned to simply get around stuff like this. Upper management doesn't even really understand what this database is for <grin> Thanks for your input, Susan Show quote "TC" <aatcbbtcc***@yahoo.com> wrote in message news:1112677916.785543.281770@l41g2000cwc.googlegroups.com... > It seems to me that this is a business process problem - not a > technical one. > >>From management's viewpoint, who are the authorized programmers for > this particular database? > > - If is is YOU, then no-one else should alter it. If they do, you do > not try to fix it: you complain to management. Just as if someone has > forced the door to your locker. You do not get out your toolkit and fix > it: you report the matter to management. > > - If it is NOT you, ie. management believes that SEVERAL people > (including you) can change the code, then, you need some kind of > agreement, or sourcecode control system, to stop evertyone's changes > from conflicting. > > Clearly, the other people do not agree that you are the only one with > the authority to change the database code. If so, adding passwords etc. > will not solve the problem. For example, they might just over-write > your password-protected database with an earlier, non > password-protected version that they kept for that purpose! So as I > said initially, this is a business process problem - not a technical > one, IMO. > > HTH, > TC > "SusanV" <svanallen@nospam-mvps.org> wrote in message Hi Susan, one way to deal with this is to work with these users. Give them news:%23rfFXHdOFHA.1040@TK2MSFTNGP12.phx.gbl... > It's not a matter of people "taking over" the project, or anything > malicious - it's more that people use existing queries to get different > info by changing parameters, then when they close the query they altered > they aren't thinking and click ok to save the changes. Not intentional, > just an oops. No one changes any modules or table design or anything like > that, just the queries. a new empty database, with linked tables (linked to yours). They can create modify all the queries they want without modifying any in the production database. -- Joan Wild Microsoft Access MVP Yeah, I *could* do that, but the nature of the project requires regular
addition of new tables, which would make this too high-maintenance in my circumstances. They can create all the queries they want (I periodically go in and clean out the "junk") so long as they don't alter the crucial ones. Putting them into functions is working for now, until I can split the DB and set up security etc. Thanks for the thought though - I'm sure someone will see it and say "Ah HA!" ;-) Susan Show quote "Joan Wild" <jwild@nospamtyenet.com> wrote in message news:O0j4h7eOFHA.4052@TK2MSFTNGP12.phx.gbl... > > "SusanV" <svanallen@nospam-mvps.org> wrote in message > news:%23rfFXHdOFHA.1040@TK2MSFTNGP12.phx.gbl... >> It's not a matter of people "taking over" the project, or anything >> malicious - it's more that people use existing queries to get different >> info by changing parameters, then when they close the query they altered >> they aren't thinking and click ok to save the changes. Not intentional, >> just an oops. No one changes any modules or table design or anything like >> that, just the queries. > > Hi Susan, one way to deal with this is to work with these users. Give > them a new empty database, with linked tables (linked to yours). They can > create modify all the queries they want without modifying any in the > production database. > > -- > Joan Wild > Microsoft Access MVP > If you have somehow put all the queries "in functions", it might be
simpler just to MDE the database. Then they can not view or change those functions. Make sure to keep a copy of the MDB, so you can make further changes. HTH, TC Yes that's exactly what I've done - all the append, delete and update
queries are in functions, and users access MDE. Now to get the rest of the vital queries (select statements) into functions. I've got a code sample for that piece, so it's more time-consuming than technical at this point. Thanks to all for their help with this! Susan Show quote "TC" <aatcbbtcc***@yahoo.com> wrote in message news:1112774111.208378.58950@z14g2000cwz.googlegroups.com... > If you have somehow put all the queries "in functions", it might be > simpler just to MDE the database. Then they can not view or change > those functions. Make sure to keep a copy of the MDB, so you can make > further changes. > > HTH, > TC >  |
|||||||||||||||||||||||
Other interesting topics