Welcome to our book on Microsoft Visual Basic and ActiveX Data Objects (ADO) programming. In this book, we’re going to see a tremendous amount of database programming using ADO—from simply moving through a database by clicking buttons in a form all the way to using the Remote Data Services (RDS) on Web servers, learning about data shaping, using hierarchical recordsets, and creating ActiveX controls that act as data sources. There’s an immense arsenal of ADO programming power in Visual Basic, and this is the book where we’ll put it to work. ADO is Microsoft’s newest database protocol, which was built to provide an easy interface to the large Microsoft database-handling package, OLE DB. ADO is a flexible standard that is intended to supersede the two earlier standards: Data Access Objects (DAO) and Remote Data Objects (RDO). In this chapter, I’m going to start examining ADO database programming by giving an overview of Visual Basic database programming in general, then taking a brief look at both DAO and RDO before turning to ADO. I’ll put all three of these protocols into historical perspective to give you an idea why ADO is so powerful and where it fits into the scheme of things.
You may wonder why there are three different sets of database protocols available in Visual Basic. As it turns out, the reason is historical. 1
Chapter one • Visual Basic Database Programming
At first, Visual Basic only supported DAO, which connected to the Microsoft Jet database engine (the database engine in Microsoft Access). Then, recognizing that there are other database types available, Microsoft created the Open Database Connectivity (ODBC) standard, and supported ODBC with RDO in Visual Basic. Finally, Microsoft saw that the Web and other forms of data—from email to Web content—were available too, and created ADO, which is a flexible standard that allows connections on the same computer, over networks, and the Web—and is intended to supersede ODBC.
In this chapter, we’ll take a look at what databases are all about, and how to create and edit them in simple ways. I’ll take a look at DAO, RDO, and ADO programming in Visual Basic in overview to create the foundation we’ll use in the rest of the book, and specifically to get an idea of what ADO programming is all about, including an overview of the ADO object model, which is what we’ll be exploring throughout most of the book. I’ll end the chapter with an overview of another important aspect of database programming in Visual Basic—Structured Query Language (SQL). SQL is integral to a lot of database programming, and some knowledge of it is essential to database programming beyond the most basic. So the question now is—just what are databases?
What Are Databases?
Like many other programming concepts, databases have become more complex over the years, but the fundamental concept is still a simple one— databases organize data for access and manipulation under programmatic or application control.
The most popular database construct is the table, and that provides our conceptual starting point. To see how a database table works, say, for example, that you are in charge of teaching a class and are supposed to store a grade for each student. You might make up a table much like this one to record the grade for each student:
are familiar, but what about BETWEEN, LIKE, and IN? I’ll take a look at them next.
The BETWEEN Clause
You use the BETWEEN clause to specify a range for acceptable values. For example, here’s how to select all the records from the Customers table where the CustomerID record starts with A (the CustomerID field is alphabetic, not numeric, in this table):
SELECT * FROM Customers WHERE CustomerID BETWEEN
"A*" And "B*"