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

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

Greg Crowe wrote:
> 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

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

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Greg Crowe" <(E-Mail Removed)> wrote in message
news:ae89d6d9-9472-4c00-b21a-(E-Mail Removed)...
>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

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

On Sep 3, 3:43*pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> 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 ... ".
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Greg Crowe" <greg.cr...@gmail.com> wrote in message
>
> news:ae89d6d9-9472-4c00-b21a-(E-Mail Removed)...
>
> >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

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

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Greg Crowe" <(E-Mail Removed)> wrote in message
news:54179b17-7f13-45ef-9018-(E-Mail Removed)...
> 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

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.

"simran" <(E-Mail Removed).> wrote in message
news:%23PZdDQ$(E-Mail Removed)...
>
> "Greg Crowe" <(E-Mail Removed)> wrote in message
> news:ae89d6d9-9472-4c00-b21a-(E-Mail Removed)...
>>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?