If you've gone beyond building simple applications in Visual Basic, you've probably started using databases in your applications. Databases provide a great deal of power for nearly every major application you have on your computer. However, working with databases in Visual Basic has always been a little confusing for the novice. The source of the confusion? Data Access Alphabet Soup. This mélange of acronyms, created (and redefined, in some cases) by Microsoft, results in hundreds of questions about how best to access data from a VB application. This column will help you understand the terms, what they mean, and how they differ in usage
ODBC - Open Database Connectivity
<POpen Database Connectivity (ODBC) is a specification for a database API. The API is an independent standard supported by a variety of product vendors, including Oracle, Informix, Sybase and Microsoft. Drivers for these databases are provided both by the vendors and by third-party companies such as Intersolv. This is one of the oldest database technologies, and it is still in wide use today. In general, machines are configured to have a Data Source Name (DSN), which specifies what type of database is being accessed, what driver to use, where it is, and so on.
OLE DB
OLE DB is a new technology designed by Microsoft that enables easy access to all types of data through a single set of interfaces. Most programmers know how to access some type of database, whether that be Access, Oracle, SQL Server or dBase. However, each one of these databases has a slightly different query language. Some are similar to standard SQL, but dBase has a different language that is not like SQL at all.
If you are working on a web server, you may wish to access data that Microsoft Index Server has produced from your web site. You may also need to access information in plain text files or in other known document types, like Microsoft Word or Adobe Acrobat. You may also run into a situation that requires data mining using Online Analytical Processing (OLAP) tools.
All of these sources of data are now accessible through OLE DB. OLE DB uses a driver called a "provider." A provider knows how data is arranged, regardless of the particular type of data. The provider translates the request it is given into a request that can be processed against the particular type of data used by the database being queried. The programmer only has to worry about submitting a request that resembles standard SQL language; the provider takes care of the rest. As OLE DB matures, expect to see OLE DB providers for all databases that use ODBC for connectivity. OLE DB is also part of Microsoft's Universal Data Access strategy, so new applications should plan to take advantage of it.
DAO - Data Access Objects
DAO is a high-level object interface that was originally designed for use with Microsoft Access databases. It uses the Microsoft Jet engine to access data. Developers have used DAO with non-Microsoft databases; however, it has not been as efficient as some of the other available access methods. Recent improvements to Jet have made its performance significantly better. For small applications, DAO works quite nicely.
RDO - Remote Data Objects
RDO is a high-level object interface that provides an information model for accessing remote data sources through ODBC. RDO offers a set of objects that make it easy to connect to a database, execute queries and stored procedures, manipulate results, and commit changes to the server. Older applications used this technology instead of DAO because of the significantly better performance in manipulating data from remote servers.
ADO - ActiveX Data Objects
ADO combines the best features of RDO and DAO in a single, high-level object interface. ADO is designed for use on all platforms, including traditional client/server applications, web-based applications, and even Windows CE and Windows Scripting Host applications. Having a single library to learn makes your knowledge far more portable. In addition, ADO makes it much easier to create multiple-language applications, i.e. an Active Server Pages application that makes use of components built in Visual Basic. Both languages understand ADO, so you can pass ADO objects (such as recordsets) back and forth without having to translate them. ADO is interlinked with OLE DB, so any database you use either has to have an OLE DB provider, or must use the OLE DB provider for ODBC.
Hopefully, this article has helped to clarify the major components of Data Access Alphabet Soup. If you're looking for a technology to learn, stick with ADO and OLE DB. Microsoft is putting a lot of effort and money behind these technologies (for now), and you can expect to see continued improvements in future versions.