using access2007 to connect to tables in sql server 2005
up to now I've been using the ;sql server' odbc driver
recently, another odbc driver 'sql server native client 10.0' was
installed on the application server

can I use this driver ?
is performance better than the driver I;m currently using ?
is there a downside to using this new driver ?
do I need to change the applications to use this native driver, or
just recreate my ODBC data sources ?

"Roger" wrote in message
news:13db5df5-3f0f-4f3e-bebe-399bbcc65ce2 (AT) s17g2000yqs (DOT) googlegroups.com...

It is a newer driver, I believe it has support for SSL and some compression.
It also has support for some of the newer features a SQL server, of none of
which you are likely using. I belive some new data types are also supported.

I do not believe there's any performance advantages. I believe is also a
possibility of some date issues cropping up - in other words when I used the
upsizing tools, and I've chosen this driver, I did have some issues and
problems with my date columns (I can not 100% say for sure the this was
result of using the new native drivers, but I believe it was my case)

There is little if any benefit here. Furthermore, if you move your
application or database to a different computer, and attempt to RE-ink
without such drivers installed, your relink code will fail. This is
especially so if you build the connection string in code.

In other words, you're better off to use the standard drivers that you have,
and not rely on a new set of freshly installed drivers that you can NOT
expect to be installed on other machines. I don't see much of benefits of
using the new native drivers.

If you just linking a few tables to SQL server, I see little harm in using
the new native drivers. However, if the application is to be run different
computers and you have some DSN less re-linking code, then you best stick
with the standard (older) drivers. This means you can pretty be assured that
your application will link and properly connect properly to SQL server when
you move the application to a different computer.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

On Jul 29, 6:42*am, "Albert D. Kallal" <PleaseNOOOsPAMmkal... (AT) msn (DOT) com>
wrote:
thanks for the information
the applications are for internal use, so IT can deploy the new driver
to all servers as required

the reason I'm asking, in the past, one of the applications has logged
this error
CONNECTION IS BUSY WITH RESULTS FROM ANOTHER HSTMT

and googling seems to suggest that the problem is related to my
current odbc driver reusing the connection before all the data from an
earlier request has been received from the sql server

and that using the native odbc driver, with the Multiple Active Result
Sets (MARS) feature enabled would solve the problem,

http://blogs.msdn.com/b/dataaccess/a...02/446894.aspx
http://msdn.microsoft.com/en-us/library/ms345109.aspx

and while researching the problem, I found that the native driver was
installed on one the servers, thus my initial questions

note, the applications use DAO recordsets throughout


I suppose the native driver could be a solution, but I need to test,
test, test...

"Roger" wrote in message
news:bc95cdf1-42a6-4eb1-a1ec-dfa84cdcb16c (AT) d7g2000vbv (DOT) googlegroups.com...

Thanks for the follow-up, since you have control of this situation, and
there might be some potential benefits of using this driver, then by all
means I would use it.

In other words, I can only warn you of the few potential downsides, however
I simply do not have any knowledge much beyond what I shared with you.

In other words all I really know is this is a newer driver, supports newer
features. And I had some issues with upsizing data to sql server using this
new driver.

However, to be fair, the only downside I can think of for the most part is
it may not be on your target machine that will be running the application
(and that not a problem for you).

If anything as you suggest you may find some advantages and benefits by
using this driver - unfortunately I don't have any more knowledge to offer
than that it is a newer driver, and could hinder distribution of your
application.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> wrote in
newsUxYp.45158$_b7.38335 (AT) newsfe22 (DOT) iad:

I think it depends on what driver you're currently using. I upgraded
a SQL Server from 2000 to 2008 R2 and the native client 10.0 was so
much faster that it was shocking to me.

So, it just depends -- test and see.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> wrote in
news:9LAYp.46554$Mo3.11007 (AT) newsfe15 (DOT) iad:

It seems to me that it's not that new a driver (it was released with
SQL Server 2008 R2, nearly two years ago).

My basic principle is that I'll use it with SQL Server 2008 R2, and
if I had older SQL Servers, I'd test it before using it. But I
really think that if you're using the latest SQL Server version,
you'd benefit most from the new driver.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"David-W-Fenton" wrote in message
news:Xns9F34AFBD2195Af99a49ed1d0c49c5bbb2 (AT) 74 (DOT) 209.136.90...

Gee, that is a rather compelling reason to consider the newer driver (note I
stress the word "newer" as opposed to "new").

About the only downside I see is when distributing Access applications to
machines that I have little control over.
However, I must say that considering the above info, then perhaps should
start looking into including these newer drivers in my install routines.
I might even consider having my re-link routines try the newer drivers first
and THEN fall back to the older sql drivers if including the drivers
proves to be a hassle or challenge.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com