ADP reports using SQL views unavailable to users

[Greg Crowe]

I have ADP front end to SQL 2005 data. This front end is a conversion
from an older MDB. The old MDB links to views on the SQL server. These
views are used as datasources for a number of reports.

The ADP links to the same views, same SQL side permissions. When run
these reports all error out that the views are not available.

I am really scratching my head on this one. What should I inspect
first in order to determine that the ADP's reports are setup
correctly?

Regards,
- Greg Crowe

 

[Robert Morley]

There may be a specific issue here that I'm unaware of, but my first thought
would be to see what happens if an Admin uses the report vs. a regular user.
Even if the permissions are properly defined, it may be some issue like
requiring the "dbo.ViewName" syntax for your report name or something of
that nature.

That's just a stab in the dark, but thought it might be something to look
at. If not, hopefully someone else will have more info.


Rob

 

[Sylvain Lafontaine]

Maybe a schema problem here: is the owner (for SQL-2000) or the schema (for
SQL-2005+) anything but dbo. on these views? If not, set the Record Source
Qualifier property of the report (under the Data tab) to the name of the
owner/schema. If nothing work, replace the name of the view for the Record
Source with a select query; something like "Select * from MyView ... ".

 

[Greg Crowe]

Thank you everyone,

Rob - with admin / develpoer the reports run fine. With standard user
as MDB the reports run fine.

Sylvain - SQL 2005 - schema is all dbo and I did try "dbo.View_Name"
to no avail.

Regards,
- Greg

 

[Greg Crowe]

Solved:

The solution to this was the much-maligned: decompile, open project
holding left-shift, compile, compact and repair, close, create new
project, import all objects into new project and set reference,
compile, compact and repair, re-release project.

I am a fresh-faced noob to ADP, but it seems like they are a LOT
buggier than MDB.

Regards,
- Greg Crowe

 

[Sylvain Lafontaine]

There are as much buggy as any MDB database file because they are
essentially using the same file format. An easier way to decompile and
compact an ADP file might be using a shortcut with the parameters /decompile
and /compact:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\MyProject.adp"
/decompile /compact

I usually execute this shortcut *twice*. It's important to do it twice
because I get a smaller size after doing it this way. After that, I'm
pretty sure that any precompiled VBA code has been left out.

 

[Paul Shapiro]

What Access version? Are you specifying the report's record source as
dbo.viewName? You can run SQL Server's SQL Profiler to watch the exact
commands being sent from Access to SQL Server.