Changing connection to SQL Native client

[arichins]

I need help in figuring out how to change the connection of an ADP project to the SQL Native Client.

I upgraded our back end to SQL Server 2005, and now, when certain stored procedures are run via the ADP project, the DB engine on the back end goes haywire, running various segments of the query over and over and over.


I've read on various forums, that we should change the ADP connection to the "SQL Native Client". So I followed this MS tutorial on how to change the ADPs connection string and re-connect http://support.microsoft.com/kb/306881, but am using the "SQLNCLI" native client instead of what they are using in this example.

But when I try to re-connect with the new string I get an connection error: "The expression you entered refers to an object that is closed or doesn't exist". Other times, I get "Method'OpenConnection' of object '_CurrentProject' failed.

I've printed out the connection string in a message box, and it's good(so please don't send me a link to the connectionstrings website, I've already been there). Access also throws an error if it's invalid, so I don't think there is anything wrong with it.

[arichins]

Upon further research, it seems that it may in fact be impossible to use the SQL Native Client with ADP projects.
http://www.pcreview.co.uk/forums/thread-2423851.php

I've spent awhile researching this issue, and so far, have found many asking the same question, but no solution. So I'm assuming it's just not possible. Though you can supposedly use it if you're doing an ADO connection from within VB.

I'll try this, and see if it fixes my bug. But it appears that ADP projects may be going the way of the Dodo. Kinda sad, I think it's a great tool.

[arichins]

Found a solution(well, kinda).

I modified one of the problem forms, and instead of using a stored procedure on SQL Server as the record source of the form, I wrote the query in VB using ADO. I set the provider to be the SQL Native Client, and the query ran much faster.

So there are indeed problems with the ole db clients that Access ADPs can use and SQL Server 2005. I wish MS would fix it, and allow ADPs to connect with the Native Client. But they seem to be dropping support for ADPs.

Now I have to figure out how to make the dataset, the record source of the form.

[boblarson]

I don't use ADP's - never have. I use linked ODBC tables into an mdb or ACCDB file and it works great. Plus the added bonus is that you can have LOCAL tables in Access where you can't in an ADP.

[arichins]

I think ADP is great personally, it's MS who has decided to drop the ball with it. If you have the rights to admin your own SQL Server, then being able to have "local" access tables is no advantage.


I like to build tables/stored procedures/views in SQL Server Directly, and then just use Access for forms/reports/data entry.

With ADP you take advantage of the power of a real DB backend, and just use access for the front end. I really disagree with MS for telling people to go back to using MDB.

With ADP, you don't have to link or refresh tables, or edit SQL passthroughs. I think Access has a bad reputation because of it's db engine, but if more people realized they could be using it as a easy development front end for a real DB, it would get more usage.

[dcb]

Originally Posted by arichins

a real DB, it would get more usage.

On a access forum ---- you are brave

[boblarson]

With ADP you take advantage of the power of a real DB backend, and just use access for the front end.[/quote]
Actually, you can do the same in an MDB file with no ill effect.

I really disagree with MS for telling people to go back to using MDB.

Well, unfortunately you'll eventually have to learn to do it the way we do it.

With ADP, you don't have to link or refresh tables

We don't have to "refresh" tables either.

or edit SQL passthroughs.

I RARELY have to use a SQL passthrough because Access CAN and DOES send an optimized query to SQL Server (just as long as you follow some rules).

I think Access has a bad reputation because of it's db engine,

Not really. I think it is MORE because it is TOO easy for users to work with and so they end up creating all of these badly written databases that end up becoming the dependent tool for a department or set of users and then the person who created it leaves and nobody can support it and then their IT Department comes into the picture and they cringe because it was such a badly written tool.

but if more people realized they could be using it as a easy development front end for a real DB, it would get more usage.

Believe it or not it gets way more usage than you might think. But as mentioned, it is that it is TOO easy to acquire and to work in that causes most of the problems.

So, anyway, I will say that I'm sure that there are some strengths that ADP's have that a linked MDB/ACCDB file does not. But at the same time there are things I can do with an MDB/ACCDB that you'll never be able to do with an ADP. So, as we know, they are on the way out and eventually you'll be one of us (if you stay with Access at all). That's just a matter of fact. But, like I said before, I've worked on some major projects, including one for NIKE using an mdb file linked to SQL Server and it works great.

[Galaxiom]

Originally Posted by arichins

With ADP you take advantage of the power of a real DB backend, and just use access for the front end.

That makes no sense to me. Maybe I am confused but I thought ADP (Active Data Page) was a web page front end. I only ever made one (out of curiosity) from an Access 2003 database using a Wizard. This had an mdb backend but I assumed the resulting page would work with whatever backend the original mdb front end used.

I really disagree with MS for telling people to go back to using MDB.

They didn't. Microsoft advised that ASP (Active Server Pages) are a better option than ADP and that is why they dropped ADP for 2007.

[boblarson]

Originally Posted by Galaxiom

That makes no sense to me. Maybe I am confused but I thought ADP (Active Data Page) was a web page front end. I only ever made one (out of curiosity) from an Access 2003 database using a Wizard. This had an mdb backend but I assumed the resulting page would work with whatever backend the original mdb front end used.



They didn't. Microsoft advised that ASP (Active Server Pages) are a better option than ADP and that is why they dropped ADP for 2007.

Galaxiom:

I think you are mistaking DAPs (Data Access Pages) for ADPs (Access Data Projects). DAPs are the web based pages that were discontinued in 2007 and ADP's (a way of directly interfacing with SQL Server) are still around but not for too much longer it would seem.

[Galaxiom]

Thanks Bob.
Definitely confused. Acronym overload.
Access loves its As and Ds. There are way too many of them.
Getting rid of two of them is as good a reason as any to drop both DAP and ADP.