ADO clients that communicate with OLE DB need an OLE DB provider, a dynamic-link library that uses OLE DB interfaces and methods to query an SQL data source. For Microsoft® SQL Server™, two types of OLE DB providers can be used:
We recommend using SQLOLEDB, supplied with SQL Server, as your OLE DB provider. SQLOLEDB maps OLE DB interfaces and methods over SQL Server data sources without using the ODBC API or the SQL Server ODBC driver as intermediaries. The ADO Introductory Visual Basic Sample application, used in most of the code examples in the ADO object model topics, uses SQLOLEDB.
There are differences between SQLOLEDB and MSDASQL. The two providers support different connection properties, and SQLOLEDB does not support the use of an ODBC DSN connection. For more information about the use of connection properties for the two providers, see Connection Object.
In general, if you are exercising ADO code with SQL Server and an unexpected behavior occurs, check the provider properties, because the unexpected behavior could be attributable to ADO interacting differently with a different OLE DB provider.