Access 2000 ADP and SQL Server 2005

[HJ]

Hi all,

We currently have a Microsoft Access 2000 ADP and SQL Server 2000. Now we
are migrating to a new database server with SQL Server 2005.

In the ADP we have reports with the record source set to a stored procedure,
e.g. uspTest.

We are testing the Access 2000 ADP against the new SQL Server 2005 server
and we cannot get the reports working. We have tried the following record
sources:

uspTest
dbo.uspTest
EXEC dbo.uspTest
EXEC dbo.uspTest;1 (since this is how the stored procedure is listed in the
database window)

Every time the error message says "The record source '<record source>'
specified on this form or report does not exist". How can we get the reports
working?

We also tested the ADP in Microsoft Access 2003; this is working very well.
But, however and very unfortunately, everbody is still using Office 2000 and
the company is not (yet) willing to upgrade.

Any help is much appreciated,

HJ

 

[Sylvain Lafontaine]

The fact that the stored procedure (SP) is stored as uspTest;1 in the
database window is an old bug of Access 2000 that has been corrected with a
service pack; I suppose that the latest SP has not been installed on your
machine. Update your computer with the latest SP - including SP for Office
and the MDAC and JET SP8 - and you should be OK. See
http://msdn2.microsoft.com/en-us/data/aa937730.aspx

There has been a big change between SQL-2000 and 2005 with regards to the
schema (dbo here in your case) of the database objects. In SQL-2000, this
is strongly related to the owner of each object but it's no longer the case
in SQL-2005

Did you try with either « EXEC uspTEST » or with uspTest but with setting
the Record Source Qualifier property set to dbo ?

You should also take a look with the SQL-Server Profiler in order to see
what's going on between Access and the sql-server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[HJ]

Yes, it has been solved. Why didn't I think of that? Anyway, installing SP3
did the trick.

Thx!