Access 2003 ADP and SQL Server 2005

[Guest]

Our office has a number of users who use Access 2003 ADPs to create/modify
views and reports on tables contained in SQL Server. We recently upgraded to
SQL Server 2005, and it appears that APDs can no longer be user to
create/modify views in SQL Server.

How can I continue to provide to our users the ability to create/modify
views and build reports on data in SQL Server 2005 using Access as a front
end? Do I have to convert to MDB's and use linked tables? If so does this
mean that any views users create cannot be stored in SQL Server?

It looks like I'm screwed here, so any suggestions are welcome.

Thanks.

 

[Brendan Reynolds]

You could check out Access 2007. I have only done some very cursory tests of
an Access 2007 ADP against SQL Server 2005. It worked, but as I say, they
were just some quick, cursory tests. I had no reason to get into it in depth
as I don't develop ADPs. So I can't promise that it will solve your problem,
but it is a *potential* solution you may want to evaluate.

 

[Guest]

I was just reading about setting the sp_dbcmptlevel to 80. Apparently this
sets SQL Server's compatibility with SQL 2000. Does this re-enable view
creation/modification via Access ADPs again?

 

[Brendan Reynolds]

I don't *think* so, but as I say I have not looked into this problem in any
great depth, as I don't develop ADPs. If no one else provides a more
definitive answer here, you might want to ask that question in the ADP
newsgroup ...

http://www.microsoft.com/communitie...aspx?dg=microsoft.public.access.adp.sqlserver

 

[dbahooker]

you should develop ADP; MDB is obsolete you MVP wimp

more importantly; Microsoft shouldn't just award MVPs to MDB script
kiddies.. I mean; I feel bad for your MDB kids anyways-- just because
you're too stupid to learn SQL Server

-Aaron

 

[Guest]

Thanks Brendan I will try that.

I don't *think* so, but as I say I have not looked into this problem in any
great depth, as I don't develop ADPs. If no one else provides a more
definitive answer here, you might want to ask that question in the ADP
newsgroup ...

http://www.microsoft.com/communitie...aspx?dg=microsoft.public.access.adp.sqlserver

 

[Van T. Dinh]

From another post by Cindy Winegarden:

" ...I'm not an Access guru but one thing I found when working with an ADP
against SQL 2005 is that the database must be in SQL 2000 compatible format
since an Access ADP isn't compatible with any schema but DBO. ..."