Database Connection Methods: ODBC/DAO/RDO/OLEDB/ADO

ODBC (Open Database Connectivity, Open Database interconnection)It is an integral part of the database in Microsoft's WOSA (Windows Open Services Architecture). It establishes a set of specifications, it also provides a set of standard APIs for database access (application programming interfaces ). These APIs use SQL to complete most of their tasks. ODBC also provides support for the SQL language. You can directly send SQL statements to ODBC. --- The earliest appearance, but not always omnipotent. Is an early database interface technology introduced by Microsoft. It is actually the predecessor of ADO.

DAO (Data Access Objects ):The data Access object is used to expose the Microsoft Jet Database Engine (which was first used for Microsoft Access and now supports other databases) and allows developers to directly connect to other databases through ODBC, directly connect to the Access table. DAO is most suitable for single-system applications or local distribution in a small range. Its internal access to the Jet Database has been accelerated and optimized, and it is also very convenient to use. Therefore, if the database is an Access database and is used locally, we recommend that you use this Access method-application uniqueness.

RDO (Remote Data Objects)The remote data object is an ODBC-oriented data access interface. It is combined with the easy-to-use DAO style and provides an interface, shows the underlying functions and flexibility of all ODBC databases. Although RDO is restricted in its access to Jet or ISAM databases, it can only access relational databases through the existing ODBC driver. However, RDO has proved to be the best interface that many SQL Server, Oracle, and other large relational database developers often choose. RDO provides more complex objects, attributes, and methods used to access stored procedures and complex result sets. --- It is undoubtedly based on odbc

OLE DBIs a strategic system-level programming interface of Microsoft used to manage data within the entire organization. Ole db is an open specification built on the ODBC function. ODBC is specially developed to access relational databases. ole db is used to access relational and non-relational information sources, such as the host ISAM/VSAM and hierarchical database, email and file system storage, text, graphics, and geographic data, as well as custom business objects.
Ole db defines a set of COM interfaces, encapsulates various database management system services, and allows you to create software components to implement these services. Ole db components include data providers (including and presenting data), data users (using data), and service components (processing and transmitting data, such as query processors and cursor engines ).
Ole db interfaces help to smoothly integrate components, so that ole db Component vendors can quickly provide high-quality ole db components to the market. In addition, ole db contains a "bridge" Connecting ODBC, which provides consistent support for various ODBC relational database drivers. --- Claim to replace odbc, but also compatible with odbc

ADO (ActiveX Data Object)Is the successor of DAO/RDO. ADO 2.0 is more functionally similar to RDO, and generally there is a similar ing between the two models. ADO "extends" the object model used by DAO and RDO, which means it contains fewer objects, more attributes, methods (and parameters), and events. As the latest database access mode, ADO is also easy to use, so Microsoft has clearly stated that it will focus on ADO in the future and will not upgrade DAO/RDO, therefore, ADO has become the mainstream of database development. ADO involves three Data Storage Methods: DSN (data source name), ODBC (open data connection), and ole db. The following routine will explain in detail the specific access implementation of these three methods. --- It can be said that it is the integration of system-level programming interfaces such as odbc and oledb, and the upgrade of application-level programming interfaces such as DAO and RDO.

----------------------------------------------------------------------------------
Performance Comparison of OLEDB

There are many ways to connect to a database. You can use System DSN, DSN-less connection or local OLEDB provider. OLEDB? What is this? Many of you have never heard of this before. To answer this question, let's first review the database connection history.

Early database connections were very difficult. the format of each database is different. Developers must have a deep understanding of the underlying APIs of each database they develop. therefore, General APIs that can process a variety of databases have emerged. that is, ODBC (Open Database Connectivity) is an early product of common APIs. many types of databases comply with this standard, known as ODBC-compatible databases.
ODBC compatible databases include Access, MS-SQL Server, Oracle, Informix and so on.

However, ODBC is not perfect. It still contains a large number of low-level calls and it is still difficult to develop ODBC applications. developers have to spend a lot of energy on the underlying database communication, rather than focusing on the data they want to process. later, Microsoft proposed a solution: DAO (Data Access Objects ). the DAO code looks like this:

ObjItem. AddNew
ObjItem. Name = "Chair"
ObjItem. Price = 10
ObjItem. Update

You may have read the DAO code. later DAO evolved into RDO (Remote Data Objects, designed for the distributed database system), and then ADO. although they all have their own shortcomings. according to Microsoft, "ODBC provides access to local SQL data, and DAO provides advanced data objects ". DAO and RDO both require data to be stored in SQL (Structured Query Language) format. to address these defects, Microsoft proposed OLEDB, a COM-based data storage object that can provide operations on all types of data and even access data offline (for example, you are using your host, and you can easily see the data image for the last data synchronization ).

OLEDB is located between the ODBC layer and applications. on your ASP page, ADO is an "application" located on top of OLEDB ". your ADO call is first sent to OLEDB and then processed by ODBC. you can directly connect to the OLEDB layer. If you do this, you will see the performance improvement of the server-side cursor (the default recordset cursor is also the most commonly used cursor. how can we directly connect to OLEDB?

To directly connect to the OLEDB layer, you must change the connection string of your connection object. Use the old method to create a connectiong object:

Dim objConn
Set objConn = Server. CreateObject ("ADODB. Connection ")

Next, we don't need to routinely look like DSN = pubs or DRIVER = {ms SQL-
Server}; UID = sa; PWD =; DATABASE = pubs; SERVER = myMachine connection string, and use the following connection string:

ObjConn. ConnectionString = "Provider = ProviderName; Data
Source = DatabaseSource; Initial Catalog = DatabaseName; User ID = UserID;
Password = Password"

For SQL:
ProviderName = SQLOLEDB
Data Source = Server Name
Initial Catalog = Database Name

For Access:
ProviderName = Microsoft. Jet. OLEDB.3.51
Data Source = Full path to. MDB file

Let's take a look at two examples. One is for Access and the other is for SQL. If your DSN-less connection string for connecting to SQL is as follows:

DRIVER = {ms SQL-Server}; UID = sa; PWD =; DATABASE = pubs; SERVER = myMachine

The connection string directly connected to OLEDB should be as follows:

Provider = SQLOLEDB; Data Source = myMachine; Initial Catalog = pubs; User
ID = sa; Password =

Let's take a look at Access. If your Access connection string is:

DRIVER = {Microsoft Access Driver (*. mdb )};
DBQ = c: \ inetpub \ wwwroot \ users. mdb

The connection string directly connected to OLEDB should be as follows:

Provider = Microsoft. Jet. OLEDB.3.51; Data
Source = c: \ inetpub \ wwwroot \ users. mdb

That's easy, right?

Is this important?
Now, you may be confused about how to learn this new database connection method. Why not go through the standard DSN-less/System DSN path? Let me tell you why. According to Wrox's ADO 2.0
In the Programmer's Reference book, the performance of connections connected with OLEDB instead of DSN or DSN-less is improved as follows:

Performance Comparison
SQL Access
OLEDB DSN
Connection Times: 18 82 Connection Times: 62 99
Iterating through 1,000 Records Times: 2900 5400 Iterating through
1,000 Records Times: 100 950