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

"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/Forums.aspx/access-security/200904/1
>
>