Permissions for Tables and Queries

Microsoft Jet stores both tables and queries in the Tables container. This section uses “tables” to refer to both tables and queries. The following permission constants are defined.

Constant Value Description
dbSecReadDef &H4 The permission to read the definition of a table.
dbSecWriteDef &H8 Or dbSecReadDef Or dbSecDelete The permission to alter the definition of a table. Note that this permission is a combination of the &H8 value and the read definitions and delete permissions. You have to be able to read the definition of an object in order to change that definition.
dbSecRetrieveData &H10 Or dbSecReadDef The permission to read data stored in the table. This permission is combined with the read definitions permission. Although you could separate the two permissions, most applications that use Microsoft Jet, such as Microsoft Access, require the ability to read the table’s definition in order to be able to read the table’s data.
dbSecInsertData &H20 The permission to insert new records into the table.
dbSecDeleteData &H80 The permission to delete records from a table. Note the subtle distinction between deleting data and replacing all fields in the record with null data, which is controlled by the dbSecReplaceData permission constant.
dbSecReplaceData &H40 The permission to modify data that exists in the table.

These permissions can be combined using the Or keyword. For example, to grant a user modify data and insert data permissions for a table, you use the following:

docCurrent.Permissions = dbSecInsertData Or dbSecReplaceData Or _
	dbSecRetrieveData

Note that the dbSecRetrieveData permission constant is included: Most programs require the ability to read data before it can be updated.

Microsoft Access Permissions

Microsoft Access defines a number of permission settings for use with its own objects. These permissions are described in the following table.

Constant Value Description
acSecFrmRptReadDef &H4 The permission to read the definition of a form or report.
acSecFrmRptWriteDef &H8 Or acSecFormRptReadDef Or dbSecDelete The permission to alter the definition of a form or report.
acSecFrmRptExecute &H100 The permission to execute the form or report.
acSecMacReadDef &HA The permission to read the definition of a macro.
acSecMacWriteDef &H6 Or acSecMacReadDef The permission to alter the definition of a macro.
acSecMacExecute &H8 The permission to execute the macro.
acSecModReadDef &H2 The permission to read the definition of a module.
acSecModWriteDef &H4 Or acSecModReadDef Or dbSecDelete The permission to alter the definition of a module.

Execute permission defines the ability to “run” the object. For example, execute permission on a form gives you the ability to open the form and browse or print it. For macros, execute permission defines the ability to open the macro and run its contents. You can see that there is no execute permission for modules. Obviously, if Microsoft Access had to check the permission for every function for a given user, performance would be severely degraded.

Permissions with the ReadDef suffix define the ability to open the object in Design mode. Unlike tables and queries, ReadDef permissions are not required to read the data displayed by Microsoft Access forms and reports.

Permissions with the WriteDef suffix define the ability to open an object in Design mode and to save any changes made to the object’s definition.

The following table shows how Microsoft Jet permissions constants map to the permissions check boxes in the Microsoft Access User And Group Permissions dialog box. It is important to note that some of these constants represent bitwise combinations of other constants as described in the other tables in this chapter.

Object Microsoft Access permission Microsoft Jet permission constant
Database Open/Run dbSecDBOpen
Open Exclusive dbSecDBOpenExclusive
Administer dbSecDBAdmin
Tables and queries Read Design dbSecReadDef
Modify Design dbSecWriteDef
Administer dbSecFullAccess
Read Data dbSecRetrieveData
Update Data dbSecReplaceData
Insert Data dbSecInsertData
Delete Data dbSecDeleteData
Forms and reports Open/Run acSecFrmRptExecute
Read Design acSecFrmRptReadDef
Modify Design acSecFrmRptWriteDef
Administer dbSecFullAccess
Macros Open/Run acSecMacExecute
Read Design acSecMacReadDef
Modify Design acSecMacWriteDef
Administer dbSecFullAccess
Modules Read Design acSecModReadDef
Modify Design acSecModWriteDef
Administer dbSecFullAccess