Queries being modified

[SusanV]

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.

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

 

[Joan Wild]

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

 

[SusanV]

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

 

[Keith]

Hi all,

Using any kind of logon security is not an option, as per management.

Susan,

You could as an alternative method still implement user-level security and
give the users account names which exactly match their network logon ID.
Put the user name in the command line "/user" switch, leave the users'
passwords null and test their network ID against the CurrentUser() function
on startup. If they don't match then quit. It's an approach I've used in
the past to get around the password issue.

Regards,
Keith.
www.keithwilby.com

 

[SusanV]

Excellent option - thanks for the suggestion, I think that will work! Now to
read up on Access Security...

Thanks Keith!

 

[Lynn Trapp]

Susan,
Another approach, besides those suggested by Joan and Keith, might also be
possible. The Security FAQ has instructions for creating a "remote site
administrator." Basically, it involves creating 2 workgroup information
files (with identical PIDs) -- using one for development and one for
distribution. You would create RWOP queries using the development workgroup
information file. You would remove the Admin user from the Admins group in
the distribution workgroup. You could give the Admin user permission to open
and run the queries, but nothing else. Then your users wouldn't need to log
on (they would be silently logged on) and they would only be able to run the
queries. They couldn't modify them.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[SusanV]

Another good concept - thanks!

Susan,
Another approach, besides those suggested by Joan and Keith, might also be
possible. The Security FAQ has instructions for creating a "remote site
administrator." Basically, it involves creating 2 workgroup information
files (with identical PIDs) -- using one for development and one for
distribution. You would create RWOP queries using the development
workgroup information file. You would remove the Admin user from the
Admins group in the distribution workgroup. You could give the Admin user
permission to open and run the queries, but nothing else. Then your users
wouldn't need to log on (they would be silently logged on) and they would
only be able to run the queries. They couldn't modify them.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Joan Wild]

I already hide the database window on startup but as you said F11 brings
it back and the more savvy users know this.

You can disable this in Tools, Startup.

 

[TC]

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

 

[Keith]

Your welcome Susan. There is a link to the MS FAQ on Access security on my
website and there's also a step-by-step example. Remeber to always work on
*copies* of your files as it's very easy to lock yourself out.

Regards,
Keith.
www.keithwilby.com

 

[SusanV]

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

 

[SusanV]

Yes I have those docs, just not the time to go over them in depth at this
point. Small company we all wear many hats, you know? And yes, I always work
off a copy, and have multiple backups every night.

Thanks for the link, however your site's been bookmarked pretty much since i
started this!

Susan

 

[Keith]

Good, sounds like you're pretty switched on. I always include that 'work on
a copy' caveat just to cover myself against the old 'help I've locked myself
out' compaints. Glad to be of help. Good luck.

Regards,
Keith.

 

[Joan Wild]

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.

 

[SusanV]

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

 

[TC]

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

 

[SusanV]

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