Advanced ADO connection to SQL server

[jordanking]

Hello,

Can anyone outline the main differences between using the following providers when connecting to a SQL server 2005 database:
SQLNCLI vs Microsoft.Access.OLEDB.10.0 provider

I am working with an access ADP and obviously a sql server 2005 back end. I have been working at a problem for days now(outlined in the following threads: thread707-1426632 and thread183-1426981). I created a stored procedure that returns a recordset and output parameters. But I could not access both, I could only get the recordset, but no output parameters. SO with some help and a lot of testing, i narrowed the problem down to the connection string.

this one works: (returns recordset and output parameters)

cnn.Open "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=REGENT01\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=RegentSigns;Data Provider=SQLOLEDB.1"

which is the same as using "cnn.Open CurrentProject.AccessConnection"


this one does not: (only returns recordset but some of its properties are unaccessable, like recordcount, and no output parameters)

Cnn.Open "Provider=SQLNCLI;Data Source=REGENT01\SQLEXPRESS;Integrated Security=SSPI;database=RegentSigns"

I am planning to package this solution for distribution in a small business environment with sql server 2005.

Can anyone maybe shed more light on why this is happening and maybe some pros and cons to each provider, like should I use one for simple select queries and another for procedures that return results.

In addition, I found that if I package the solution with the access 2007 runtime I have to install the SQl server native client in order to use the SQLNCI provider. I assume the microsoft.access provider is packaged together when the project is complied.

I appologize if this is out of the scope of this forum,

thanks in advance.

JK




.....
I'd rather be surfing

 

[jordanking]

SOLUTION,

if anyone else wants to know...

The real problem was in my references. I had a reference to "Microsoft ActiveX Data Objects 2.6 Library."

I needed to change it to the 2.8 library, and I included the Recordset library as well. Then both the recordset and output paramters were passed back to Access from SQL server 2005.

Furthermore, i found some of the main differences between the two above mentioned data providers.

Most documentation said that SQLNCLI was faster, and if you needed access to any of the following new features to SQL server 2005, you need the SQLNCLI:
-MARS (multiple active result sets)
-UDT (user defined data types)
-XML (XML sql server datat type)
-Query Notifications
****this is by no means a comprehensive list***

THe microsoft provider is adequate if you are not interested in any of these features.


If you want to have access to all the new features in sql 2005, you need to include the folloing parameters in your connection string:

DataTypeCompatibility=80;
MARS Connection=True;


.....
I'd rather be surfing