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.

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" wrote:

> 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 wrote:
> >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.
>
> --
> Message posted via http://www.accessmonster.com
>
>

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.


"Chris O'C via AccessMonster.com" wrote:

> 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 wrote:
>
> >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.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...urity/200904/1
>
>