Security Issues When Using Code in Access Reports

  • Thread starter Thread starter DDJ
  • Start date Start date
D

DDJ

I believe I've read that when automating Access reports from VB it is
recommended that developers put as much of the code as possible in Access
for performance reasons. Assuming this is true, are there any issues when
distributing the VB app to end users that we should be concerned with? I'm
specifically thinking of "macro" type security warnings, etc. to users.
These security warnings only apply when using macros, vs. using code,
correct?

Our plan is to create a database password for the related Access database,
and open the database via code in non-exclusive mode when the user wants to
preview the report. If I understand correctly, the user will not be able to
modify and Access objects, including report design, and report code, if we
do it in this manner, correct?

Thanks for any feedback!

Dan
 
Dan:

1.) In Access 2003, the security warnings can occur even if using VBA due to
Access' security model. Your users would need to set their macro security
for Access (which in Access and all other Office apps includes VBA) to "low"
to avoid the prompts. Previous versions of Access do not have this issue.

2.) When you say "create a database password", it is unclear what you mean.
There are two types of "passwords" if you would available for Access.
First, you have a basic password you can set to open a database. This
password is global and once the db is open doesn't stop anyone from changing
anything in the database (its also pretty easy to crack). The second type
of password are really based on the Access Security Model, where you can set
permissions on various objects such as form / reports, tables etc. and who
can open and/or view or change them. This is a very sophisticated model.
At the same time, its is a bit more difficult to open an Access database
using automation that uses this type of security, when what you want to do
is to display items in the UI and allow individuals specific permissions on
target objects.

Automation doesn't support specifying use of a specific system database
which would normally be required to apply the permissions specified using
the Access security model (See the MS KB using key words "automation" and
"system database". You can work around this in the Access model by giving
everyone read permissions on all tables/queries/report etc. If you set it
up that way, then even if the db is opened using automation, user's could
preview and print reports, but they couldn't change anything with objects.

Hope that helps.
 
Thanks for your response Steve. See below...

SA said:
Dan:

1.) In Access 2003, the security warnings can occur even if using VBA due to
Access' security model. Your users would need to set their macro security
for Access (which in Access and all other Office apps includes VBA) to "low"
to avoid the prompts. Previous versions of Access do not have this issue.
Click to expand...

Desparately wanted to avoid this, but guess I should have known better. Is
it possible to determine in VB whether the user will have this problem in
advance (so I can add code providing the user with a "how to" to fix)?
2.) When you say "create a database password", it is unclear what you
Click to expand...
mean.

I meant a database password. Don't know why I was thinking this would
prevent modification to database objects...
There are two types of "passwords" if you would available for Access.
First, you have a basic password you can set to open a database. This
password is global and once the db is open doesn't stop anyone from changing
anything in the database (its also pretty easy to crack). The second type
of password are really based on the Access Security Model, where you can set
permissions on various objects such as form / reports, tables etc. and who
can open and/or view or change them. This is a very sophisticated model.
At the same time, its is a bit more difficult to open an Access database
using automation that uses this type of security, when what you want to do
is to display items in the UI and allow individuals specific permissions on
target objects.
Click to expand...
We're using Access in this specific instance solely for previewing and
printing reports. We do not want the user to be able to change the report
or related table objects.
Automation doesn't support specifying use of a specific system database
which would normally be required to apply the permissions specified using
the Access security model (See the MS KB using key words "automation" and
"system database". You can work around this in the Access model by giving
everyone read permissions on all tables/queries/report etc. If you set it
up that way, then even if the db is opened using automation, user's could
preview and print reports, but they couldn't change anything with objects.
Click to expand...

In the past I've had problems setting up these permissions in such a way
that I can continue editing the various objects, while restricting what can
be done when opening the db via code (opening using the "read-only" user's
name and password). Would you agree I can do this WITHOUT having to ship
the mdw file with the app?
 
DDJ:

1.) You can read the registry to determine the security level, its stored
in:

HKEY_Current_User\Microsoft\Office\11.0\Access\Security

2.) On the mdw, yes, you could set up a situation where only you have the
design permissions on form/reports and everyone else (normally the user
called "Admin" which is the default user) has read/write permissions, but
keep in mind that you'd want to give read/write on the tables. Once you
set this up with your mdw, you can distribute the db without it and
effectuate the permissions so long as you retain the standard default user.

Of course a word of caution and that is that because you may not stay at
your job forever, do the next person a favor and write down the mdw values
and back it up once you create it so that if they have to go in an modify
the thing, you've left them the ability to do so.

Conversely you could also create a simple report MDE, which is attached to
the data which would solve most of this without the Access security model.
 
Hadn't thought about using an MDE. If our objective is to

1) Design the report in Access, and
2) Open and populate the report from VB, and
3) Let the user either preview or print the report (without being able to
modify and db objects).

....it sure seems like the MDE will work great, particularly if we can avoid
security warnings by doing so. Would this apply to Access 2003 installation
too (no security warnings)?

Thanks,

Dan,
 
Back
Top