We help IT Professionals succeed at work.
Get Started

vba ado provider to sql server -- ole and native client

Doug Foster
Doug Foster asked
on
345 Views
Last Modified: 2016-08-11
i haven't tested the performance recently.. but I want to throw this question out there to get perspective.

I have an Excel/vba app that runs reports off of a sql server warehouse in a corporate environment, using windows authentication.  

I'm using an ADO connection to the db, and have alternated between using the OLE (SQLOLEDB) and the native client (SQLNCLI10) provider.

It's worked fine with both providers for 6 years, and I haven't noticed a significant performance difference, although I haven't been scientific in benchmarking them.

But now some people have been upgraded from version 10 to version 11, and now it doesn't work for them, since it is version specific.  

That will be a pain to maintain, and I'll have to come up with a creative way to try more than one and turn off  On Error.  

I know that OLE has been depreciated, but it seems to me it has the significant advantage of not being version specific in the connection string.

Is there a way to use the native client that is not version specific?  And is it truly better than using OLE?  When will OLE be phased out?

Thanks...
Comment
Watch Question
"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
Unlock 3 Answers and 11 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE