prevent user from creating objects

In a secure database, user "admin" is a member of "users" group only.  "Admin" and "users" have no permissions for any new objects (new tables/queries, etc.).  I thought this would prevent the creation of new objects by "admin".  This is not the case.  What am I missing?  
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

From what I understand, you cannot keep a user who has full access to the database from making new tables, queries, and macros.

What you can do is, as the owner, turn on With Owner Access for all queries so that only the owner, you, can muck with queries.  For tables, I would remove all permissions then make queries which allow you to get into the tables with owner access.  Forms, Macros, Reports, and Modules should have open/read only.

Be sure that the database itself has only open/run permissions also.

To further keep users out of my tables, I split them off into another db and link into them.  However, I don't give any permissions to them.  If the user tries to open the linked DB, I put in an autoexec macro to tell them they can't be here and then close the DB.

To keep them from making objects in the main DB, I remove the database window and turn off all menus, command bars, etc. and put them immediately into a code module.  The only way out is to close the app.  They can not get to the database window.

To prevent the users from bypassing the startup routines, I disable the Shift Key on both DB's.  Both of the DB's are turned into MDE's which further keeps users out.

I will be creating a third DB, which will be an MDB.  It will have only reports.  This way they can make their own reports through program control only.  It needs to be an MDB so that they can create or modify the reports.  It will link back to the queries of the main DB.

I hope that I have helped you understand it a little more.  BTW, since I don't want to deal with sending out MDW's, which can be broken, I don't use them and any user with a default MDW or whatever can access the app.

In Access 97, the "users" group typically has full permissions, which includes the permission to create new objects of any kind. Have you removed that capability from your users group?
Brewdog:  I thought that we were both talking about permissions for the 'users' group.  I know I was.

I double checked it myself and even with no permissions allowed, a member of the 'users' group, with no other individual permissions, can, if they have access to the database window, can create new table, query, and macro objects.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

I was closing your loop, Jim. You said

From what I understand, you cannot keep a user who has full access to the database from making new tables, queries, and macros.

But jstiefel did not say what permissions his users group has.
jstiefelAuthor Commented:
To JimMorgan and Brewdog:
To quote from my original question, ""Admin" and "users" have no permissions for any new objects (new tables/queries, etc.)."  I am in the process of printing JimMorgan's response and figure out how much this might help me.  I will reply at that time. Thanks a bunch for the responses.
I am aware that you set it with no permissions for any new objects.  I did the same thing in my test db.

Unfortunately the only objects you can prevent users from creating are
Tables and Queries. You do this by removing the DB_SEC_CREATE (dbSecCreate in Access 97) permission
from the Tables Collection. (See Permissions Property in Access Help)

The users can go through the steps of creating the table or query but
they are unable to save the new object.

I have found the easiest way to handle your problem is as follows:

You must ensure that you have split your database into a 'data' database
and a 'forms' database. That way you won't be overwriting any of their
data. Make a copy of the 'forms' database available to your users but
also have your own copy of the 'forms' database.

This allows you to make changes to the copy of the 'forms' database then
make it available to users when it is ready. Any new objects that the
users have created will disappear because you will overwrite the users'
database with your copy.

It is best to let users know that it is fruitless for them to create new
objects because they will automatically disappear whenever you decide to
replace their copy of the database with your copy. That way, if they
feel there is a need to create new objects, they will have to approach
you so that you include it in your version of the database.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jstiefelAuthor Commented:
To JimMorgan and nunga

I apologize for taking this long to reply but I am fighting off Access projects that people need at work.  Also, neither answer gives me a pat answer to my problem.  I naively thought that this situation would be fairly common and therefore fairly easy. My situation has some other conditions that complicate this.  I am going to give the 200 points for this question to nunga but will post another question for JimMorgan only with the same points since I will probably use some of both answers to, hopefully, come up with a work through of the situation.  Also, I have learned from both answers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.