Record source ... does not exist

[tunsted]

I'm having problems running reports in an Access 2000 ADP where the report's recordsource is a parameterized stored procedure (SQL 7/2000).

The parameters are being passed via the report's Input Parameters property, and are derived from a dialog box. This was working fine until the database was moved to a new server (backed-up and restored). Now I'm getting an error message "The record source '<<Stored Procedure Name>>' specified on this form or report does not exist" each time I try to run the report.

I've checked permissions, logins, 'dbo.' prefixes, etc. and can find nothing unusual. The stored procedure can be selected as the recordsource for the report in the ADP, and runs fine in SQL Query Analyser. The ADP has been opened and closed many times since moving the database, so it shouldn't be a problem with caching.

Any suggestions would be greatly appreciated!

 

[TimKThailand]

Did you see the sp name in the Queries tab?
If it's not there, you might have to delete the user group from the db and add it back again. This happens sometimes when you moved the database around.

 

[tunsted]

Thanks, but I'm not sure what you're referring to...

This is an Access Data Project (.adp) that uses an SQL Server database rather than a Jet database.

There is no Queries tab, but in the list of objects in the database window you see the Tables, Views and Stored Procedures from SQL Server (as well as Forms, Pages and Modules).

Yes, in the list of Stored Procedures I can see the stored procedures that I'm using for the reports.

Using SQL Profiler I can see that they are being executed on the Server when the report is opened, and are being passed the expected parameters, but Access then returns the error message.

 

[TimKThailand]

If you can see it, can you run it separately?
If you can run it, then make sure you have add dbo in Record Source Qualifier property of the report.

My ADP has the Queries tab and all Views, Stored Procedures, Functions are kept in there.