Lock Users Out

[Robert_DubYa]

I have used Access for a number of years now and consider myself "handy" with
it. Finally other people in my company are starting to catch on. In the
past my security has only been to not allow full menus and start access with
a form. Recently someone has figured out how to get around the start up and
deleted a number of queries that ran a rather important report.

I would like to restrict the ability to delete or modify queries, reports or
forms. I have thought about restricting write access on the disk level, but
I need to allow the user write access as I have make table queries running.

Please excuse the fact that I am very green to secuirty issues.

thanks,
Robert

 

[Craig]

Robert,

First off, you need a development db and a user db. When your done making
your updates, run this code. Also enter in the real db path.
Dim db as dao.database

Set db = DBEngine.OpenDatabase("c:\my db path")
Set myProp = db.CreateProperty("AllowBypassKey", dbBoolean, False)
db.Properties.Append myProp
db.Properties("AllowBypassKey").Value = False

set db=nothing

This works for both .mdb and .mde files.

 

[Robert_DubYa]

Hi Craig,

Thanks for response.

Could you please explain the function of the development and user db's? Are
you talking about making an fe and be db? If so I should explain that I am
only using this procedure as a report. The actual tables are in my company's
ERP system and I am connecting via odbc to get a live report. Because the
tables in our ERP system are so large and ODBC is so slow over our network I
use make table queries that run off of a click event. I fear using a be db
would slow this process down.

Now what has happened is someone has deleted a number of the queries that
run when the report is kicked off. All I want to do is make it so the
queries can not be deleted. I have tried using the security wizard (I hate
wizards) on test cases, but I can't get the wizard to work correctly (It
keeps locking me out!)

Your help is very much apperciated,
Robert

 

[Joan Wild]

Hi Craig,

Thanks for response.

Could you please explain the function of the development and user
db's? Are you talking about making an fe and be db? If so I should
explain that I am only using this procedure as a report. The actual
tables are in my company's ERP system and I am connecting via odbc to
get a live report. Because the tables in our ERP system are so large
and ODBC is so slow over our network I use make table queries that
run off of a click event. I fear using a be db would slow this
process down.

You are already using a backend db - the tables are in the ERP system. Even
if you create local tables, your database is still split. You should have
at the very least a backup of the mdb you are using. Then all you'd have to
do is restore the backup.

You, as the developer, should have your own copy of the mdb. You can make
changes, test things, and once you are ready, you'd copy the updated
frontend to the users. If someone deletes something they shouldn't (and
just why do they get away with this, BTW?), you'd be able to deploy the
latest mdb.

You could implement security, but you may not have to. Instead of using
saved queries, you could run the sql statements in code - create a mde of
the mdb, and distribute that - they wouldn't be able to modify the code.
However that wouldn't stop them from deleting things.

You can lock down the interface, so that they don't see the database window.

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)

If you need to bypass these startup features, you can hold the shift key
down while you open the db. If you feel that your users may use this to
bypass your settings, 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

If you create a MDE from your database, be certain to keep your original
mdb in case you need to make changes.

None of this will keep the determined out. All they need to do is start a
new db and link to your's, but this may suffice for your purposes.

Perhaps management can intervene with regard to the deleting.