> I've also seen suggestions to basically create a simple
> "select *" query with RWOP in the front end for each and every table
> in the back-end, and then use those queries as the record source for
> other queries in the front end in lieu of the tables themselves. My
> question is two-fold: does this tactic completely wipe out the
> problems with RWOP queries? Can I then continue to create SQL
> statements in code, pointing to the RWOP queries, which in turn point
> to the tables in the back-end? Does everything then work just as if
> I was pointing directly to the back-end tables?
Yes, you just need to put the appropriate permissions on the RWOP queries.
> Does this
> method provide a real security benefit... i.e. does it truly prevent
> linking or importing the raw data, or at least provide a reasonable
> hindrance to the average user in doing so?
A reasonable hindrance...I would say so. You can always prevent the user
from creating new objects (section 24 of the Security FAQ), while using that
Microsoft Access MVP
I wrote this utility several years ago to convert a finished app to use RWOP
queries and set the various startup options to lockup the db. It may have
something you can use.
It is in A97 so may need converting to your version of Access.
Using COPIES (for safety) of your Fe/Be, import all objects from the utility
into your FE and run afrmLockDatabase.
Gosford NSW Australia
I'm afraid to say I'm a bit confused by this. I see the relevant
information in the FAQ, but the way I read that, it is code inserted
into the database (in this case, my mde) that prevents new objects from
being created within that database. Are you saying code can be
inserted and automatically run in an mdw? If so, what prevents someone
from creating a database from an unsecured mdw, closing it, then
re-opening it with my shared, secure mdw, then importing objects from
my secured database? IF code can be inserted into an mdw to prevent
them from creating new databases, can code also be created in an mdw to
prevent them from importing or creating NEW links while using that mdw?
Sorry for the stupid questions, but the though of being able to insert
permissions and other forms of control into an mdw is new to me... I
thought all it could do was keep a list of users, groups, and
passwords. If it can do more than that, then that certainly opens up
some security options.
Nothing <smile> But if you have used RWOP queries, then they won't have
permissions on the tables, so can't import them (which is what you concern
was, right?). If you make a MDE, then they won't be able to see the design
However, I'm still intrigued by idea of preventing users from creating
new databases while attached to my mdw. And I still don't follow how
to do that, as the security faq seems to be talking about preventing
users from creating objects while using my MDE, not my MDW. There's
apparently a 'hole' in that as a security measure, as you just
acknowledged, but I'd still like to understand it. How exactly do you
create permissions that are "tied" specifically to an MDW?
I've never noticed an impact.
> However, I'm still intrigued by idea of preventing users from creating
> new databases while attached to my mdw. And I still don't follow how
> to do that, as the security faq seems to be talking about preventing
> users from creating objects while using my MDE, not my MDW. There's
> apparently a 'hole' in that as a security measure, as you just
> acknowledged, but I'd still like to understand it. How exactly do you
> create permissions that are "tied" specifically to an MDW?
I've never actually implemented that, but what you're doing is preventing a
user from creating objects while joined to, or using that mdw, as I
understand it. Perhaps I have it wrong, and it prevents the user from
creating objects just in that database.
OK, a bit more digging and yes you can deny the creation of a database while
using a particular mdw. Basically you set the permission on the database
container of the workgroup file (rather than the current database).
The obvious question is, why do I care if I'm going to set tables to
owner permissions only? And the answer is: I simply like throwing as
many speed bumps at a potential snooper as I can. There's no way I can
make a desktop database completely secure, so the best I can do is make
it as troublesome as possible, in the hopes that the vast majority of
potential snoopers decide it's just not worth their time.
Sorry for the dumb questions - as I said before, the concept of
permissions, or any other editable attribute, of a workgroup file, is
new to me.
Microsoft Access MVP
> Thanks for digging in to that! Although I'm afraid to say I don't
> know how to reference the mdw's container.
Instead of Set db = Currentdb()
Set db =
Then set the container to
>And another part that
> confuses me is, the security faq says that the database has to be
> compiled as an mde to be able to set permissions for creating
Where? Not in my copy.
> Does a new user automatically have
> "create database" permissions individually that would override any
> group settings?
> Sorry for the dumb questions - as I said before, the concept of
> permissions, or any other editable attribute, of a workgroup file, is
> new to me.
I really haven't played with this enough to answer your questions. You
could test this out yourself, and hopefully come back and share what you've
"Where? Not in my copy."
Admittedly, I may be using the wrong version. The only one I've ever
been able to find is the one listed specifically for versions 2.0
through 2000 here:
I've searched for "security faq" in the Access 2003 knowledge base,
and only get two returns (neither of which is a security faq). I'm
sure there's a newer one that I'm just not able to find, but to answer
your question, in the older one, under section 24, it says: "There is
no way to remove permission to create forms, reports, macros, or
modules in Microsoft Access unless you compile your database as an MDE
in Access 97 or Access 2000." Of course, the letter of that caveat is
limited to forms, reports, macros, and modules, but that is all that
the paragraph insinuates that you can limit: it never mentions the
ability to restrict database creation that you've been so kind to shed
light on. But again, perhaps I'm not looking at the CORRECT security
"You could test this out yourself, and hopefully come back and share
what you've found?"
Yes, I will have to do that. Obviously the "hole" mentioned earlier
prohibits this from being a true security measure, but like most of the
other "security" options in Access, it could be one more deterrent to
make a snooper decide it's just not worth the time. :-)
Nope that's the latest.
> your question, in the older one, under section 24, it says: "There is
> no way to remove permission to create forms, reports, macros, or
> modules in Microsoft Access unless you compile your database as an MDE
> in Access 97 or Access 2000."
Well that really isn't about our discussion. That's referring to a MDE.
Create a MDE, and users can't import those objects. It doesn't mean that it
must be a MDE to prevent creation of a new database.
> Yes, I will have to do that. Obviously the "hole" mentioned earlier
> prohibits this from being a true security measure, but like most of
> the other "security" options in Access, it could be one more
> deterrent to make a snooper decide it's just not worth the time. :-)
Indeed, that's the best you can do. It usually is sufficient for most users
of Access. I've never come across one that wanted to. They seem happy to
have a tool that helps them do their job.