Unanswered: Which OBDC driver should I use? SQL Server vs Native Client
I have an Access front end talking to a SQL Server 2005 back end (which may get updated to 2008 R2 sometime next year).
Is there any benefit to using the "SQL Server Native Client 10.0" driver vs the "SQL Server" driver? In one location there was a passing reference to speed, but I have not been able to confirm it any where else. Microsoft indicates that the Native client is only needed to use some of the new advanced features introduced back in SQL Server 2005.
The problem, of course, is that I need to get IS to install Native Client on any machine that wants to use my front end. If there is a real advantage (especially in speed, even more so in speed over WAN) then I'll go through the hassle. Otherwise, it is probably not worth it.
It depends on which Data Access Library (DAO or ADODB) you use and how you use it. If you work with DAO, there is no difference and that won't change if you switch to a SQL Server 2008 R2.
I read somewhere that "SQL Server Native Client" offers new or improved functionalities when working with ADODB, at least for its newest versions (forget about 2.1). As I seldom use ADODB in my applications, I'm no specialist of it and I'm not able to be more explicit on that topic.