ACC1x: Cannot db.OpenTable with Only Read Definition Permission

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you have only Read Definition permissions on a table, you are unable to open the table in order to examine the table definition from Access Basic and you may receive the following error message:

   No permission for '<Tablename>' (versions 1.x)

CAUSE

The table cannot be opened from Access Basic when you have only Read Definition permission. You should have users Read Data permission for the table if you need to examine the table definition with Access Basic.

If you have Read Definition permission, you can only look at the table definition manually by opening the table in Design view as read-only.

STATUS

This behavior is by design.

NOTE: This behavior does not exist in Microsoft Access version 2.0 because of the ability to use data access objects (DAO) in Access Basic to obtain the table definition. Instead of attempting to open a Table variable (as shown below) which includes the data, you can open a Tabledef object, which does not include data. Therefore, no error will occur.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open a new database and create a table called Table1.

2. Add a new user named Joe and give this user Read Definition permission only on the Table1 table.

3. Quit Microsoft Access, and restart Microsoft Access and log on as Joe. (Be sure you have removed all permissions from the group Users because everyone who logs on to Microsoft Access is a member of the group Users.)

4. Enter the following code in a new module:

         Option Explicit
         Sub Test ()
            Dim Mydb As Database
            Dim MyTable As Table
            Set Mydb = CurrentDB()
            Set MyTable = mydb.OpenTable("Table1")
            MyTable.MoveFirst
            MyTable.Close
            Mydb.Close
         End Sub
   
   

5. Run this Sub procedure by typing "test" (without the quotation marks) in the Immediate window. Note that you receive the following error message displayed:

         No permission for '<Tablename>'