Problem Implementing Security

[Glen Denning]

I've encountered a problem implementing security related to reports in
an Access 2000 mdb file. This file is a front-end with links to tables
in another mdb. It was subjected to the Access Security Wizard, then re-
created as an import into a new mdb to ensure permissions were properly
set. Prior to implementing security, the file was developed and tested
on computers using Win98 and WinXP, both running Office XP.

My mdb includes a report which is modified by VBA in design view, saved
and closed, then opened again to show it to the user in Preview Mode.
The modifications consist of setting textbox values, textbox visibility
and setting an OrderBy string based on what the user selects on a form.

The problem report displays properly when I'm logged on as a user in the
Admins group. But when I'm logged into a Read-Only User group I've
created, the VBA fails silently (without raising an error) while saving
the report. The result is that my changes don't appear when the report
is re-opened.

Members of the Read-Only User group have Open/Run, Read Design, and
Modify Design permission set for the report and new reports. This,
however, doesn't appear to allow them to save the modified report. With
regards to the report, the only difference between Admin and members of
the Read-Only User group is that that the latter doesn't have Administer
rights. (Granting Administer rights doesn't fix the problem though.) Any
suggestions?

Glen

 

[Joan Wild]

I've encountered a problem implementing security related to reports in
an Access 2000 mdb file. This file is a front-end with links to tables
in another mdb. It was subjected to the Access Security Wizard, then
re- created as an import into a new mdb to ensure permissions were
properly set.

The security wizard in 2000 is flawed and shouldn't be relied on.
Furthermore, when you imported it into the new mdb, all permissions would be
lost (they don't come with the import). So your subsequent problem seems
odd to me.

My mdb includes a report which is modified by VBA in design view,
saved and closed, then opened again to show it to the user in Preview
Mode. The modifications consist of setting textbox values, textbox
visibility and setting an OrderBy string based on what the user
selects on a form.

These properties can be set at runtime without modifying the design of the
reports. In the Open event you can
Me.txtFoo.ControlSource = whatever
Me.txtFoo.Visibility = false
Me.OrderBy = Forms!someForm!lstSort
Me.OrderByOn = True

 

[Glen Denning]

Joan,

The security wizard in 2000 is flawed and shouldn't be relied on.
Furthermore, when you imported it into the new mdb, all permissions would be
lost (they don't come with the import).

Yes, I read about that in Microsoft's article "Frequently Asked
Questions About Microsoft Access Security for Microsoft Access versions
2.0 through 2000." After following all their steps, including setting
permissions for several groups, I had to import the file into a new
container and *reset/re-create* all the permissions. This was supposed
to overcome the Security Wizard flaws (and made me wonder why I was
using it in the first place).

These properties can be set at runtime without modifying the design of the
reports. In the Open event you can

Thanks for a good suggestion . . . using the Open event eliminated the
problem. I'd guess it's also a less resource-intensive way to achieve
the same result. I should also be able to remove Read Design, and Modify
Design permission from the report.

I did re-create the secured database from scratch without using the
Security Manager. I still experienced the same problem with Read-Only
Users being unable to save a report via VBA from design mode, despite
having Open/Run, Read Design, and Modify Design permission for the
report.

Instead of trying to change the report via code, I opened the report
directly in design mode while logged on as a Read-Only User. Prior to
showing the report in design mode, Access advised that

"You do not have exclusive access to the database at this time. If
you proceed to make changes, you may not be able to save them later."

I made a few changes, tried to save, and Access advised

"Microsoft Access can't save design changes or save to a new
database object because another user has the file open. To save your
design changes or to save to a new object, you must have exclusive
access to the file."

No other user had the file open, but when I let the Read-Only group open
the front end exclusively (which is how it would be used anyway), the
users were able to save the report from design view. Ah ha!

Thanks for your help! The information in your answer and many of your
previous posts is very useful in understanding the circuitous path
through Access security.

Glen

 

[Joan Wild]

After following all their steps, including setting
permissions for several groups, I had to import the file into a new
container and *reset/re-create* all the permissions. This was supposed
to overcome the Security Wizard flaws (and made me wonder why I was
using it in the first place).

Indeed ;-)

Thanks for a good suggestion . . . using the Open event eliminated the
problem. I'd guess it's also a less resource-intensive way to achieve
the same result. I should also be able to remove Read Design, and
Modify Design permission from the report.

This is desireable I would think. Also if you make a mde I don't think you
can make design changes.

"Microsoft Access can't save design changes or save to a new
database object because another user has the file open. To save your
design changes or to save to a new object, you must have exclusive
access to the file."

No other user had the file open, but when I let the Read-Only group
open the front end exclusively (which is how it would be used
anyway), the users were able to save the report from design view. Ah
ha!

Well maybe. Really, you shouldn't have a mdb being shared by multiple
users. You should split the mdb into frontend (everything but the
tables)/backend (just the tables/relationships). The backend would be put
on a server share. Give each user a copy of the frontend (linked to the
backend) on their computer. That way they will/can have the frontend opened
exclusively and make design changes. You see, new in 2000, you must have
exclusively opened the database to make design changes to objects. Once
it's split, multiple users *can* use the backend and add/update data in it.

If you do split it, don't use the splitter wizard since you have a secured
database. The wizard will result in a secure frontend, but the backend will
be totally unsecured. Split it manually.