Applications using the Open Database Connectivity (ODBC) API can access Microsoft® SQL Server. ODBC is an implementation of database API standards defined by X/Open and the International Standards Organization (ISO). Applications that are coded to the ODBC API can use DBMS-specific drivers to access different databases without being rewritten to use the proprietary API of each DBMS.
Microsoft SQL Server includes an ODBC driver that allows ODBC applications to work with SQL Server data. The performance of ODBC applications using the Microsoft SQL Server ODBC driver is equal to the performance of applications using the older DB-Library API; therefore Microsoft supports ODBC as a native API for Microsoft SQL Server.
ODBC is a fairly low-level API, and several other Microsoft database APIs are built over ODBC. These APIs are either easier to code than ODBC, or offer tighter integration with their development language. Through its ODBC driver, Microsoft SQL Server also supports applications written to these APIs:
The OLE DB API is similar to ODBC. It allows an application to access data from heterogeneous sources without being rewritten to use the proprietary API of each data source. While ODBC is designed for C applications, OLE DB is designed to be used in C++ applications. ODBC also requires that a data source support at least a minimum set of SQL statements, and therefore is used primarily to access relational and ISAM databases. OLE DB does not force this use of SQL, and therefore supports a wider range of nonrelational data. OLE DB includes an OLE DB ODBC provider that allows OLE DB applications to use existing ODBC drivers. OLE DB applications can use the OLE DB ODBC provider along with the Microsoft SQL Server ODBC driver to access Microsoft SQL Server. The Microsoft Active Data Objects (ADO) API is built on OLE DB in the same manner that DAO and RDO are built on ODBC. Microsoft SQL Server also supports ADO applications using the Microsoft SQL Server ODBC driver.
These APIs offer a rich set of functions you can use to:
While the ODBC-based APIs are more portable than DB-Library and offer roughly equal performance, there is no compelling reason to convert existing DB-Library applications. Microsoft recommends that new SQL Server applications, or significant rewrites of DB-Library applications, be written to the ODBC-based APIs rather than the older DB-Library-based APIs. The use of DB-Library-based APIs should be limited to maintaining existing DB-Library applications, or for applications that require the DB-Library bulk copy functions.
The primary database API currently used by Visual C++ applications is the MFC Database Classes. The MFC Database Classes in Visual C++ version 5.0 do not support ODBC 3.0, so applications needing functionality available in ODBC 3.0 must be coded directly to the ODBC 3.0 API.
In Visual Basic version 5.0, the RDO API offers the best performance and functionality when accessing Microsoft SQL Server. RDC is a simpler API that has almost the same performance as RDO when used with Microsoft SQL Server. While the current version of ADO does not support as much functionality as the current RDO, ADO will be the primary Visual Basic database API in the future and will be enhanced to support all the current RDO features. If the current version of ADO supports all the functionality you need, consider using it instead of RDO. The factors to be considered when choosing a Visual Basic Database API are discussed in detail in a whitepaper "Choosing the Right VB5 Data Access Interface" available at http://www.microsoft.com/vbasic/techinfo.
Applications using any of these ODBC-based APIs are subject to the specific behavior of the Microsoft SQL Server ODBC driver. This behavior is discussed in both the SQL Server manuals and the whitepaper "Using ODBC with Microsoft SQL Server" at http://www.microsoft.com/sql/reskit.htm.