ADO and OLE DB Providers

ADO and OLE DB Providers

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:

Microsoft OLE DB Provider for SQL Server (SQLOLEDB), which directly maps OLE DB interfaces and methods over SQL Server data sources.
Microsoft OLE DB Provider for ODBC (MSDASQL), which maps OLE DB interfaces and methods to ODBC APIs. OLE DB consumers connect to a SQL Server database through an existing SQL Server ODBC driver.
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.

Technorati 標籤: 程式設計, 資料庫設計