View Single Post
Old 05-08-2007, 04:01 PM
loquin's Avatar
loquin loquin is offline
Google Hound

Retired Moderator
* Guru *
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default Accessing the Data

Buzzwords and acronyms. Data Access in Visual Basic is simply drowing in them. DAO. ADO. Data Providers. OleDB. ODBC. JET. ADODC.... The list goes on and on. Why can't we just make this Simple!!! Shoot, when I was growing up, a buzzword was what you got when you said something while playing a kazoo.

Well, lets give simplification a whirl. The first thing to remember is that Microsoft has been TRYING to make it simple for VB for a long, long time. Since even before VB was released. But, data access often isn't as simple as it sounds. Sure, the underlying concept can be fairly straight-forward. Add a new record. Edit a record. Delete a record. View a record. In and of itself, any of these basic database operations isn't that difficult. However, when dealing with databases, remember that a database usually doesn't exist in isolation.

Often, more than one user (client) can be using the database at one time. What do you do when two users are trying to edit the same table. What about when they're trying to edit the same record? These complex issues, as well as many others, will force applications programmers to utilize database access libraries.

Keep in mind - once upon a time, the situation was WAY more complex than today. Every database had its own means of performing the basic operations! Which meant that if I wrote an application that interfaced to a dBase database, it was a real chore to make that app work with Paradox or BTrieve. In order to try to standardize the confusion, Microsoft introduced a unified interface to several databases called ODBC. ODBC is an acronym for Open Data Base Connectivity. It is essentially an API which allows windows programs a standard interface to access a database. In order for the single API to be able to talk to multiple databases, each database type required a custom interface to be written. This interface, or ODBC driver is called by ODBC to read and write to the database. So, many database vendors each wrote a driver for their particular database, which adhered to the ODBC standards defined by Microsoft. And 'All was well in the database world.'

Yeah. Righhhttt. I have a bridge to sell, too...

There was one litle issue. Even though writing database applications WAS easier when you have a common interface, ODBC was designed around ISAM (Indexed, Sequential Access Method) databases like dBase, FoxPro, and, MS Access, with its JET (Joint Engine Technology) database engine.

The ODBC model was designed to provide efficient access to ISAM databases and Access was designed as an ISAM database to take advantage of this. However, as database servers like Oracle and Sybase came onto the scene, the ODBC model just didn't 'fit' as well. In addition, in order to use ODBC, you HAD to do so by using its API, in the form of a lot of low-level C programming. It just was not a very easy process to master.

Microsoft had also released a Rapid Application Development tool called Visual Basic. I'm sure you've heard of it. As more and more developers realized its potential, Microsoft released a library to allow VB to interface to databases. DAO (Data Access Objects) were closely modeled on ODBC, and allowed VB Programmers to take advantage of the ODBC interface, without using low-level code.

However, the shorcomings of the ODBC model as it related to database server technology still existed. Yes, it was possible to interface to database servers, but, the interface wasn't necessarily very efficient. In order to close this gap, and allow efficient use of database servers, which tend to be much more scaleable than ISAM databases, Microsoft released a new (shortlived) object model library called RDO, (Remote Database Objects. The RDO object model more closely aligned itself to server-based database systems. This was accomplished by using Microsoft's Component Object Model (COM) via the OleDB (Object Linking and Embedding) Database API.

However, Microsoft soon realized that other potential data sources, like legacy database systems, directory structures, log files, and the like would also benefit from being able to access them using a standard data interface, so they released ADO, or ActiveX Data Objects, to provide a standardized interface to ISAM databases, Server-based database, non-traditional data sources, and legacy systems alike. Microsoft often referred to ADO as a means for 'Universal Data Access.' ADO, available since VB5, allows the application programmer to use the same interface library to access a wide-ranging set of potential data sources.

OleDB supports these various data sources with OleDB Data Providers, which are equivalent to ODBC's drivers. They provide the data specific interface, while ADO provides the common access functionality.

Now that you've have a Microsoft history lesson please refer the attached diagram. It is a schematic which outlines many of the ways that you can access a database from within VB. Note that the diagram is intentionally incomplete. It does not, for instance, contain any reference to RDO. Also, please be aware that the data sources in the database layer show a great deal of overlap that cannot reflected in a simple diagram. Some ISAM data sources have many relational features, while some relational databases store their data internally in an ISAM format. And, some of the the 'Non-Relational' data sources may exhibit some relational traits.
Attached Images
File Type: jpg ADO Object Model.JPG (43.8 KB, 104 views)
Attached Files
File Type: pdf ADO Object Model.pdf (12.1 KB, 97 views)
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 06-21-2007 at 09:49 AM. Reason: clarification
Reply With Quote