Microsoft KB Archive/243532

= ACC2000: Cannot Open Report in a Microsoft Access Project =

Article ID: 243532

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q243532





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  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.

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



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are 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 can t cannot

Keywords: kbbug kbclientserver kbpending KB243532

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.