Arji
asked on
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= 'ACTestDat a';Trusted _Connectio n=Yes;"
This works in both MSDE and SQL2k
strCnxn = "Provider='sqloledb';Data Source='AC2000SBS';Initial Catalog='ACTestData';Integ rated 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.
This works only in MSDE
strCnxn = "DRIVER={SQL Server};Server='AC2000SBS'
This works in both MSDE and SQL2k
strCnxn = "Provider='sqloledb';Data Source='AC2000SBS';Initial
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.
ASKER
Hey Jim,
I did go to connectionstrings.com and found this under SQL server:
Under ODBC:
"Driver={SQL Server};Server=Aron1;Datab ase=pubs;T rusted_Con nection=ye s;"
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.
I did go to connectionstrings.com and found this under SQL server:
Under ODBC:
"Driver={SQL Server};Server=Aron1;Datab
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.
ASKER
Jim,
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.
-a
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.
-a
No suggestions/objections.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.connectionstrings.com has all of the templates for you to use.