Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Prohibiting Users and Groups from Creating Objects

You might want to prevent the members of a workgroup from creating new databases or creating specific database objects. You can prevent users from creating databases or other objects only by using VBA code.

For example, you might want to prevent users from creating new tables, queries, or other objects in your application or data database files. Listing 28.29 illustrates this process.

Listing 28.29 Prohibiting Users from Creating Other Objects

Sub NoTables(strGroupToProhibit) On Error GoTo NoTables_Err Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection cat.Tables.Refresh cat.Groups(strGroupToProhibit).SetPermissions Null, _ adPermObjTable, adAccessDeny, adRightCreate NoTables_Exit: Set cat = Nothing Exit Sub NoTables_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description Resume NoTables_Exit End Sub

The code uses the SetPermissions method of the Groups collection of the Catalog object to set permissions for new tables. The parameters used in the example deny table creation rights for the group specified in the call to the function.