ADP's with SQLServer 2005.

[jjjax0330]

Hi, just started looking to upgrade our sql server to 2005 and still using Access 2000 .adp's for a lot of our in house programs. I did the upgrade in our test system and while the connection seems to show the the tables and views fine although it is not letting me go into design view, it does seems to have problems with the stored procedures and has changed the naming to have a ;1 after the name and when I go to open, it says that it can't find it? I also, came across a article saying that .adp's might be eliminated in newer versions of access? Thanks, Joe

 

[IT4EVR]

SQL Server 2005 and SQL Server 2005 Express do not play well with Access ADPs. In fact, the SQL Server team at Microsoft has bluntly stated that ADPs will not be supported in the future.

Frankly, if you are going to use SQL Server 2005, using Access in any form for a front end just doesn't make sense. I'd download a free copy of VB.NET Express or C# Express.

 

[lameid]

The only article I saw in the MSKB said that design changes were not supported. It suggested using other tools to manage objects.

As for the object names, I would try making a new ADP and pointing it to the 2005 database. Hopefully object names are right. If not, I wonder if maybe your are experiencing a permission problem?

Please keep us apprised as to your status... I have a solitary ADP in SQL 2000 and I may need to upgrade too. I do not want to have to rewrite it (and to think the only reason I made it an ADP is for a Full Join).

 

[jjjax0330]

What I found out so far is that Access 2000 ADP's will work with SQL 2005 but needed to install office 2000 sp3. I was only on 1/1a so that seemed to clear the ;1 issue and now allows me to do basically what I need with ADP's. I also downloaded the beta version of Office 2007 and while they don't make easy to figure out how to do it, you can create ADP's with it so we're good for at least the next version of Office. I haven't gotten into full blown testing yet but will probably go forward with the SQL 2005 upgrade and continuing using Access 2000 ADP's until Office 2007 comes out then do the upgrade to Access 2007. Then start looking to change to VB.Net for our frontend.