Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Cannot Open Report in a Microsoft Access Project

ACC2000: Cannot Open Report in a Microsoft Access Project

View products that this article applies to.

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.

↑ Back to the top

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.

↑ Back to the top

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.

You can use the following subroutine to update all existing forms and reports.

Note This code assumes the owner is not already specified in the RecordSource property and all objects have the same owner.
Sub AddOwnerPrefix()

Dim x As Object
Dim strOwner As String

strOwner = "dbo."

'Change the RecordSource property for all forms.
For Each x In CurrentProject.AllForms
   DoCmd.OpenForm x.Name, acDesign, , , acFormPropertySettings, acHidden
   Forms(x.Name).RecordSource = strOwner & Forms(x.Name).RecordSource
   DoCmd.Close acForm, x.Name, acSaveYes
Next x

'Change the RecordSource property for all reports.
For Each x In CurrentProject.AllReports
   DoCmd.OpenReport x.Name, acViewDesign
   Reports(x.Name).RecordSource = strOwner & Reports(x.Name).RecordSource
   DoCmd.Close acReport, x.Name, acSaveYes
Next x

MsgBox "Finished updating all forms and reports."

End Sub

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

↑ Back to the top

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.

↑ Back to the top

Keywords: KB243532, kbpending, kbclientserver, kbbug

↑ Back to the top

Article Info
Article ID : 243532
Revision : 4
Created on : 1/26/2005
Published on : 1/26/2005
Exists online : False
Views : 268