How can I prevent user from adding table

[Patrick]

I know how to give/take away user permission for tables, queries, forms,
reports and macros, but how do I prevent user from adding new table(s) to
database. Any suggestion would be greatly apprieciated.

 

[Chris O'C via AccessMonster.com]

Run the vba code listed in the security faq to prevent users from creating
new tables and queries. Do it for both the default admin user and the users
group. Run the code when logged in as the db owner.

Public Function revokePermOnNewObj()
Call noNewObjects("Admin")
Call noNewObjects("Users")
End Function


Public Function noNewObjects(strUser As String)
Dim db As Database
Dim con As Container

Set db = CurrentDb
Set con = db.Containers("Tables")
con.UserName = strUser
con.Permissions = con.Permissions And Not DB_SEC_CREATE

Set con = Nothing
Set db = Nothing
End Function

If you haven't read the security faq yet, download and *study* it.

http://support.microsoft.com/?id=207793

Chris

 

[Patrick]

Hi Chris,
Thank you for your help, but this is too good it lock out user from opening
database with run-time error '3033':
You do not have the necessary permissions to use the 'Tables' object. Have
your system adminstrator or .....establish the appropriate permissions for
you.

but if I comment out the line below it let user open database with no
permission to create new tables and query.
con.Permissions = con.Permissions And Not DB_SEC_CREATE

How can I allow user to create queries but not table? Thanks again for your
help.

 

[Chris O'C via AccessMonster.com]

The code I gave you doesn't remove open/run database permissions from users.
Something else is affecting that. Check that you copy/pasted the code
exactly, with no modifications.

If a user can open a db without permissions, the db isn't secured in the
first place.

Do you have an object named "tables"? Besides the tables container, I mean.

"How can I allow user to create queries but not table?"

You can't with Access security. You can upsize the tables to SQL Server and
inside SQL Server allow only certain users to create tables. The users would
be free to create queries inside Access, providing you don't run the code I
gave you to prevent table and query creation.

Chris

 

[Patrick]

Got it, I gave Users open/run database permissions and it works. Regarding to
not being able to let user create queries because of Access security, I think
it's ok cause I only need to use the codes you provide with some of the
databases but not all. Thanks for your help, I really appreciated it.