ACC2000: Cannot Open Report in a Microsoft Access Project (243532)

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

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.