by Nancy Winnick Cluts

Data Interface Objects 101

Posted March 30, 1999     To be archived May 3, 1999

In a past column, I talked about the basics of databases. Now that you know what a database is and why you simply can't live without one, it's time to take a look at the widgets that allow you to access and manipulate the data in those databases from your Web page or application: data objects.

This article will explain what the different data objects are, when you use them, and why they are useful.

Data objects can be confusing -- what with all of those pesky acronyms. For instance, what's the difference between ADO and DAO, and what do they have to do with ODBC? This article is intended to explain what the different data objects are, when you use them, and why they are useful.

Definitions

First, we need to take a look at the terms we will use in this article. Whenever you come across some acronym you are unfamiliar with, the best place to look is a glossary/siteguide/glossaries.asp). Our glossaries don't contain every acronym or term you will encounter, but they contain most of the common ones. If you run into terms you don't understand, and that aren't in the glossaries, please let us know so that we can add them. Many of these terms are also defined in the article Welcome to Acronym Hell Non-MSDN Online link. In addition, check out the Universal Data Access (UDA) Web site Non-MSDN Online link for comprehensive information about Mircrosoft's data access strategy.

The following terms will be the basis of our discussion:

Microsoft Universal Data Access Family

Java Database Connectivity

Data Access Objects

ODBC: A Standard for Database Connectivity

From the definition above, you know what ODBC is, but you are probably wondering why it even exists. In the past, companies used a single database management system (DBMS) to access databases via front-end applications written specifically for the system. These were huge, monolithic systems. After a while, companies ended up with a variety DBMSs; some were cheaper, some were faster, some were software that came with a reorganization or merger. Add the personal computer to this mix, and you had many different types of computers using a variety of tools and databases. At this point, businesses needed to make their database accessible to many different types of systems using differing tools.

Developers who had to write database software that spanned these types of computers were forced to author specialized versions of their applications for each DBMS used and to write DBMS-specific code. This was a nightmare to write, debug, and maintain. Developers needed a way to access data in different DBMSs without having to rewrite their application code. That's where ODBC came in.

What is ODBC?

ODBC is the API used to create Data Access Objects. ODBC is an industry standard based on the Microsoft Windows Open Services Architecture (WOSA). ODBC contains the API, documentation, a Data Source Administrator (available via the Control Panel in Windows NT and Windows 95), drivers, test tools, and samples. The Data Source Administrator is used to manage data sources -- including setting up data source names, specifying drivers to be used, and setting timeout values. ODBC drivers are files that implement the ODBC API for the specific database management service used. Test tools are provided that allow you to tune your database access. Samples are provided to demonstrate some common ways of using the ODBC API. Want more details? Check out the documentation Non-MSDN link.

ODBC is designed so that you can use a single application to access a variety of DBMSs with the same source code. Database applications that use ODBC call functions in the ODBC interface. The interface is implemented to run with database-specific modules (drivers). This helps isolate your database access code from the particulars of a specific database. It's akin to writing an application that writes to a printer -- you use the Win32 API to call into the printer driver. The code you write is generic for all printers while the driver handles translating that code into something that the specific printer understands.

ODBC versus DAO

ODBC allows you to access a data source (local or remote) for any application that has an ODBC driver. There are ODBC drivers available for 16-bit as well as 32-bit systems, and for a wide range of data sources. DAO works best with databases that use the Microsoft Jet database engine (such as Microsoft Access). If you are working with a Microsoft Access database, DAO is much faster than ODBC. On the other hand, if you are going to be using a variety of data sources, ODBC is the more generic solution.

OLE DB

OLE DB is the underlying technology used to access various data sources across the enterprise. This means that you can access data from a database on a Web server or on another machine on your LAN. OLE DB is comprised of a few core components that provide the most basic data connectivity services and are required in any redistribution of OLE DB (see the documentation for specifics on redistribution). There are OLE DB providers for Oracle data, ODBC data, and SQL Server data, as well as to Microsoft Jet data.

Using ADO and RDS

Okay, so you've got the definitions. Now, what the heck do you do with these objects? In short, you use them to hook up your data in your database(s) with applications or Web pages. You can manipulate data without using these objects, but you would need to do all of the grunt work yourself. Instead, the objects are available to make it easier for you to "not sweat the details" but to concentrate on the actual data and how you are going to present it to users.

ADO provides the following built-in objects:

Using the objects described above, you can set up a data source (i.e., a database on a server), connect to the database via the Connection object, gather information from the database via the Command and Parameter objects, and use the resulting Recordset objects to figure out what to do with the data. If you want to manipulate one column (i.e., the babysitter's fee in the example above), you can use the Field object to sort based on that criterion. Finally, if you get multiple errors on a call (i.e., for some reason the database command could not complete and something else was wrong), you could use the Error object to find out what happened. For detailed information about ADO, see the documentation Non-MSDN link.

You can program ADOs from a standard programming language, such as Visual Basic, Visual C++, or even in Visual J++, using the Windows Foundation Classes, or you can include ADOs via script. The ADO documentation contains a very helpful tutorial Non-MSDN link that takes you through the ADO programming model and guides you through samples using the above-referenced languages and tools. In addition, the documentation includes a whole host of ADO code examples, including using an active connection, executing commands, transmitting data, and filtering data. In the example (from the tutorial) listed below, Visual Basic is used to open a connection, create a SQL statement, and execute it. 

Public Sub main()

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

' Open the connection 
conn.Open "DSN=pubs;uid=sa;pwd=;database=pubs"

' Create a SQL command 
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * from authors"

' Execute it 
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic

' Manipulate the data
rs!au_lname.Properties("Optimize") = True
rs.Sort = "au_lname"
rs.Filter = "phone LIKE '415 5*'"
rs.MoveFirst
Do While Not rs.EOF
    Debug.Print "Name: " & rs!au_fname & " "; rs!au_lname & _
        "Phone: "; rs!phone & vbCr
    rs!phone = "777" & Mid(rs!phone, 5, 11)
    rs.MoveNext
Loop

' Update (transmit) the data
conn.BeginTrans

' Conclude the udpate
On Error GoTo ConflictHandler
rs.UpdateBatch
On Error GoTo 0

conn.CommitTrans

Exit Sub

' Conclude the update
ConflictHandler:

rs.Filter = adFilterConflictingRecords
rs.MoveFirst
Do While Not rs.EOF
    Debug.Print "Conflict: Name: " & rs!au_fname; " " & rs!au_lname
    rs.MoveNext
Loop
conn.Rollback
Resume Next

End Sub

RDS

RDS provides the services to ADO that allow data to be transferred from the client, manipulated and then returned back to the server in one round-trip. RDS also allows you to specify the proxy that is used to transfer the data, and automatically retrieve specified data via a default server program. The documentation for RDS includes a tutorial that walks you through creating a simple RDS application Non-MSDN link.

That's All, Folks!

That's all there is to it. It's not really difficult, just confusing to deal with all of those acronyms. Once you get them straight (i.e., figure out the difference between ADO and DAO), you are on your way to understanding how data access works. Hopefully, you now have a good idea of what all of these objects are, and whether you want to take advantage of them.

As far as I'm concerned, all of these acronyms have left me exhausted. I'm thinking that it's time for a change of pace. Perhaps even giving up writing in favor of the new and improved sport of roller derby.

 

Be real nice to Nancy Winnick Cluts, developer-technology writer extraordinaire, and maybe she'll take you for a ride in that gorgeous red convertible.


Archived Geek Speak Decoded columns

1999
February 16    Geek Speak Decoded #7: Tasks, Processes, and Threads
January 13 Geek Speak Decoded #6: Databases 101
1998
November 15 Geek Speak Decoded #5: Welcome to Acronym Hell
October 13 Geek Speak Decoded #4: Events, Properties, Methods, Functions, and Procedures
August 3 Geek Speak Decoded #3: Client-Side and Server-Side Objects
July 17 Geek Speak Decoded #2: Debugging
March 17 Geek Speak Decoded #1: What's an Object Model?


Photo Credit: Katie McCullough/Katie McCullough Photography


Back to topBack to top

Did you find this material useful? Gripes? Compliments? Suggestions for other articles? Write us!

© 1999 Microsoft Corporation. All rights reserved. Terms of use.