SQL Server driver vs. sqloledb driver connection question

Posted on 2005-05-02
Last Modified: 2012-06-27
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.
Question by:Arji
    LVL 65

    Expert Comment

    by:Jim Horn
    Access and SQL are distinctly different connection strings. has all of the templates for you to use.
    LVL 17

    Author Comment

    Hey Jim,

    I did go to 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.

    LVL 17

    Author Comment

    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.

    LVL 65

    Expert Comment

    by:Jim Horn
    No suggestions/objections.
    LVL 1

    Accepted Solution

    Closed, 125 points refunded.
    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now