[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

SQL Server driver vs. sqloledb driver connection question

Not sure I understand why the one connection string works with MSDE but doesn't for SQL 2000:

This works only in MSDE
strCnxn = "DRIVER={SQL Server};Server='AC2000SBS';Database='ACTestData';Trusted_Connection=Yes;"

This works in both MSDE and SQL2k
strCnxn = "Provider='sqloledb';Data Source='AC2000SBS';Initial Catalog='ACTestData';Integrated Security='SSPI';"

Can't seem to find anything that explains this in my research.  The oledb driver seems to be slower in MSDE so I would prefer not to use it with SQL but I will if I have to.

BTW, the error is "Provider not available or not installed properly" when I use the first string in SQL.  Also, I'm using Access 2k3.
  • 2
  • 2
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Access and SQL are distinctly different connection strings.

http://www.connectionstrings.com has all of the templates for you to use.
ArjiAuthor Commented:
Hey Jim,

I did go to connectionstrings.com and found this under SQL server:

Under ODBC:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

This is the string that doesn't work with SQL Server but does with MSDE.  I've tried apostrophes and no apostrophes and neither works with SQL.  There is nothing about the SQLOLEDB connection.

ArjiAuthor Commented:
I used what was suggested at connectionstrings with still no luck.  I just don't understand why the string works in MSDE but not SQL 2k2.  If it's ok with you, I just want to close this question.....unless you have other suggestions.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No suggestions/objections.
Closed, 125 points refunded.
Community Support Moderator

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now