The difference between OLE DB and ODBC

ODBC (Open Database Interconnect): An early Database interface technology introduced by Microsoft. It is actually the predecessor of ADO. Early database connections are very difficult. Each database has a different format, and developers have a deep understanding of the underlying API for each database they develop. As a result, generic APIs that can handle a wide variety of databases have emerged. Now ODBC (Open Database Connectivity), ODBC is an early product of people creating generic APIs. There are many types of databases that comply with this standard, known as ODBC-compliant databases.

OLE DB (Object linking and Embedding database) is located between the ODBC layer and the application. In your ASP page, ADO is an "application" on top of OLE DB. Your ADO call is sent to OLE DB before being processed by ODBC. You can connect directly to the OLE DB layer, and if you do, you'll see a performance boost for server-side cursors (the default cursor for the recordset and the most common cursor).

To connect to a database with ODBC:
There are three types of DSNs available in ODBC, the difference is simple: the user DSN can only be used for this user. The difference between a System DSN and a file DSN is that the connection information is stored in a different location: The System DSN is stored in an ODBC storage area, and the file DSN is placed in a text file.
The way they are created is not said.
When using them in ASP, the following is the notation:
A.sql Server:
Using the system dsn:connstr= "DSN=DSNNAME; Uid=xx; Pwd=xxx;database=dbname "
Use the document Dsn:connstr= "FILEDSN=XX; Uid=xx; Pwd=xxx;database=dbname "
You can also use a connection string (thereby eliminating the need to establish a DSN):
Connstr= "Driver={sql SERVER}; Server=servername; Uid=xx; Pwd=xxx "
B.access:
Using the system dsn:connstr= "Dsn=dsnname"
(or for: connstr= "dsn=dsnname; Uid=xx; Pwd=xxx ")
Use file dsn:connstr= "Filedsn=xx"
You can also use a connection string (thereby eliminating the need to establish a DSN):
Connstr= "Driver={microsoft Access DRIVER};D Bq=d:\abc\abc.mdb"

Connect to the database with OLE DB:
A.sql Server:
Connstr= "PROVIDER=SQLOLEDB;
DATA Source=servername; Uid=xx; Pwd=xxx;database=dbname "
B.access:
Connstr= "Provicer=microsoft. JET. oledb.4.0;
DATA Source=c:\abc\abc.mdb "
It is worth noting that OLE DB's compatibility with ODBC allows OLE DB to access existing ODBC data sources. The advantage is obvious, because ODBC is more common than OLE DB, so you can get an ODBC driver that is more appropriate than OLE DB. This does not necessarily mean that the OLE DB driver will be able to access the existing data system immediately.
The provider is located at the OLE DB layer, and the driver is located in the ODBC layer. If you want to use an ODBC data source, you need to use an OLE DB Provider for ODBC, which will then use the appropriate ODBC driver. If you do not need to use an ODBC data source, you can use the appropriate OLE DB provider, which is commonly referred to as the local provider (native provider).
It is clear that using an ODBC provider means an additional layer is required. Therefore, when accessing the same data, an OLE DB Provider for ODBC may be slower than the local OLE DB provider.

The difference between OLE DB and ODBC