ACC97: "No Read Permission on..." Error After Compacting Database

• Microsoft Access 97

SYMPTOMS

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

When you try to open a form or report, you may receive the following error message:

Record(s) can't be read; no read permission on '<TableName>'

CAUSE

You may experience this problem when the following conditions are true:

• You try to open a form or report.
  
  -and-



• The record source for that form or report is set to an SQL statement that uses WITH OWNERACCESS OPTION.
  
  -and-



• You do not have at least Read Data permissions on the table.
  
  -and-



• The database has been compacted since you last saved the form or report.

RESOLUTION

Instead of having the SQL statement as the record source of the form or report, create a query with the SQL statement. Then use the query as the record source of the form or report. This will guarantee that the form or report record source is not deleted if the database if compacted.

MORE INFORMATION

The SQL statement behind forms and reports creates a hidden query when you save the form or report. The WITH OWNERACCESS OPTION in the record source informs Microsoft Access to run the hidden query. When you compact the database, this query is deleted. The query will be created each time the form or report is saved.

Steps to Reproduce Problem

Create the Database

1. Start Microsoft Access. Log on as Admin.



2. Create a new database named OwnerPer.mdb.



3. Create the following new table named tblNames:




   Field Name   Data Type
   ----------   ---------
   FirstName       Text 

4. Open the tblNames table and enter the following data:




   Name: Nancy
   Name: Andrew 

5. Create a new form named frmNames and set the following form properties. (Accept the default for all other properties.)




   Form Properties
   ---------------
   RecordSource: SELECT * FROM tblNames WITH OWNERACCESS OPTION; 

6. Add the following control to the frmNames form and set the properties of the control as indicated. (Accept the default for all other properties.)




   Text Box
   --------
   Name: FirstName
   ControlSource: FirstName 

Set Permissions

1. On the Tools menu, point to Security, and then click User And Group Accounts.



2. Add a new user to the Users group named Janet.



3. On the Tools menu, point to Security, and then click User And Group Permissions.



4. Give Janet the following permissions:




      tblNames: no permissions
      frmNames: Open/Run only 

5. Give the Users group the following permissions:




   tblNames: no permissions
   frmNames: Open/Run only 

6. On the File menu, click Exit.

Reproducing Error Message

1. Start Microsoft Access. Log on as Janet.



2. Open the database OwnerPer.mdb.



3. Open the form frmNames.




   Note that you are able to scroll through and edit the records. 

4. On the Tools menu, point to Database Utilities, and then click Compact Database.



5. Open the form frmNames.




   Note that you receive the error message described in the "Symptoms"
   section. 

Additional query words:

Keywords : kberrmsg kbdta FmsProb RptProb
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb