ACC2000: Can't Open Report in a Microsoft Access Project

ID: Q243532

The information in this article applies to:
  • Microsoft Access 2000
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).


SYMPTOMS

When you open a report in a Microsoft Access project, you may receive the following error message:

The recordsource recordsource specified on this form or report does not exist.
However the table, view, or stored procedure that the report is based on does exist in the database that the Access project is connected to and is spelled correctly.


CAUSE

The object specified in the RecordSource property of the report does not include the owner's user account name in the object identifier, and the user attempting to open the report is not the object owner.


RESOLUTION

Always specify the user name of the owner of the object when you provide an object name for the RecordSource property. For example, instead of typing MyTable in the RecordSource property, type dbo.MyTable.


STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.


MORE INFORMATION

SQL Server 7.0 and Microsoft Data Engine (MSDE) databases can contain multiple objects that have the same name, but which are owned by different user accounts. When you open a report in which the object name in the RecordSource property is not prefaced with a user account name, Microsoft Access incorrectly assumes that the object that the report uses as a record source is owned by the current user. If the object is owned by the current user, no error occurs, but if it is owned by a different account, you receive the error message documented in the "Symptoms" section.

Steps to Reproduce Behavior

  1. Open an Access project connected to the sample pubs database (Make sure that the SQL Server or MSDE user account used in the connection is a dbo for pubs; the user "sa" functions in this capacity).


  2. Click Tables under Objects, select the titleauthor table, and then on the Insert menu, click AutoReport.


  3. Save the report as titleauthor, and then view the RecordSource property of the report in Design view. Note that no owner name prefaces the titleauthor table name.


  4. Close the report, and then on the Tools menu, point to Security, and click Database Security.


  5. On the Server Logins tab, click Add.


  6. In the Name box, type TestUser, and then click SQL Server authentication under Authentication.


  7. Click the Database Access tab, and then click to select the Permit check box for the pubs database.


  8. Click Public in the Permit in Database Role list, and then click Properties.


  9. Click Permissions, and then locate the titleauthor table in the Object list.


  10. Grant SELECT permissions on the titleauthor table by clicking to select the appropriate check box.


  11. Close each security dialog box by clicking OK.


  12. Close the report.


  13. On the File menu, click Connection, and then change User Name to TestUser. Click OK.


  14. Click Reports under Objects, and then try to open the titleauthor report. Note that you receive an error message.


  15. Open the report in Design view, and then type dbo.titleauthor in the RecordSource property.


  16. Save the report, and then open it. Note that the report now opens without errors.


Additional query words: pra

Keywords : kbdta AccessCS
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug


Last Reviewed: November 4, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.