ADO Programming in Visual Basic 6

PREFACE:

Introduction

Welcome to our book on ADO database programming with Visual Basic. Activex Data Object (ADO) programming has a special connection with Visual Basic, and we'll see both packages at their best and most powerful in this book.

These two packages—ADO and Visual Basic—are specifically designed to work together. Visual Basic bends over backwards to make using ADO easy, and I'll cover the Visual Basic database tools like data views, data environments, the query builder, and more in depth. For example, Visual Basic data environments—often not even covered in general database programming books—are especially powerful in ADO, and we'll see that many techniques that are difficult in code, such as creating involved hierarchical recordsets, are simple using Visual Basic data environments.

In fact, the natural environment for ADO programming is Visual Basic—and I think you'll agree as you read this book.

What's in This Book

This book covers ADO programming using Visual Basic, which means there's a great deal of material to work with, from the basics of the ADO object model to the Remote Data Service (RDS), from using the ADO data control to working with shaped data, from creating disconnected recordsets to sorting and searching data, from the ADO object set to the ADO extension (ADOX) object set. ADO is intended to be the future of data access as far as Microsoft is concerned, and we'll see it all here.

Here's a list of some of the topics you'll see in this book:

The ADO object model
The Visual Basic database tools
The Visual Data Manager
Data Environments
DataViews
Creating database tables
Editing databases
Updating databases
Adding and deleting records
The Query Builder
The Data Form Wizard
Database diagrams
The Data Report Designer
The Query Builder
Data projects
Multi-tiered database architecture
Database transactions
The Microsoft Transaction Manager
The Microsoft Visual Modeler
Data validation
The ADO data control
Binding controls
The DataBindings collection
Using DataList, DataCombo, and DataGrid controls
Using the Hierarchical FlexGrid control
The Connection object
Connecting to databases
The Command and Parameter objects
The Recordset object
Creating and using recordsets
Executing commands
Sorting recordsets
Filtering recordsets
Searching recordsets
Creating and using disconnected recordsets
ADO Field objects
ADO Error objects
ADO Property objects
ADO dynamic properties
ADO collections
Creating data objects
Creating data source Activex controls
Creating data consumer Activex controls
Using commands with parameters
Handling recordsets, connections, and commands asynchronously
Navigating in recordsets
Using bookmarks
Batch updating
Using the ADO library in code
The properties, methods, and events of all ADO objects
Handling ADO events
Creating and using indexes
Synchronizing with the server
Persisting Recordsets
Creating recordsets on the fly
Exporting recordsets
Using Data Source Objects (DSOs) in Web pages
ADO and Active Server Pages (ASP)
The Remote Data Service (RDS)
Data shaping
Hierarchical recordsets
Child recordsets
Multiple child recordsets
Grandchild recordsets
ADOX-ADO extensions

ADO is a library of data objects—Activex and COM objects—that provides an interface to the underlying Microsoft OLE DB database access standard (the successor to ODBC), as we'll see in Chapter 1. ADO provides access to all kinds of heterogenus data using standard database techniques. It's not yet as a complete set of database access objects as other database objects sets you can use in Visual Basic, such as the Data Access Object (DAO) set, but it's growing every day. We'll see what ADO is capable of in this book, from the basics through ADO data shaping and handling child and grandchild recordsets. Chapter 1 will show where ADO fits in to Microsoft's data access scheme.

Besides showing ADO at work, this book also functions as an ADO reference. I'll cover each ADO object in detail, as well as all the ADO properties, the ADO methods and what arguments you pass to those methods, as well as the ADO events and what arguments are passed to the handler procedures for those events. The idea is that you'll be able to easily find the information you need, in depth and ready to use.

What You'll Need

To use this book profitably, you'll need a knowledge of Visual Basic programming. You don't have to be an expert, but you will need the basic skills of a Visual Basic programmer, since this book is not about teaching Visual Basic. However, when it comes to more advanced techniques like constructing Activex controls from scratch, I'll work through the Visual Basic code in depth. If you find things becoming obscure, you might want to take a look at a general Visual Basic text before continuing.

I'll use Visual Basic 6.0 in this book, because previous versions don't support the tools and interfaces needed. For that reason, you'll need a working installation of Visual Basic 6.0 or later, ideally, the Microsoft Visual Basic 6.0 Enterprise Edition. In addition, the database tools, libraries, and controls we'll use in this book can be very memory intensive, so I recommend that you use a machine with at least 64MB of RAM, although you can get away with less than that for much of the book.

Some of the examples in this book use the Microsoft SQL Server, because the native Microsoft Jet database engine that comes with Visual Basic isn't powerful enough to support many aspects of ADO programming. I'll use SQL Server 7 in this book, but you can also use SQL Server 6.5. You don't need SQL Server to use this book, but some examples and some projects on the CD will use it. If you have access to SQL Server, such as over a network, that's fine; if not, you can read through the examples as presented in the book.

In addition, I'll take a look at the Data Source Objects (DSOs) that come with the Microsoft Internet Explorer version 4.0 and later which let you use ADO with Web pages. To work through the examples in Chapters 11 and 12, you'll need that browser or later; note that Microsoft Internet Explorer 4.01 comes with Visual Basic 6.0.

I'll take a look at working with ADO and Active Server Pages (ASP) in this book, and if you want to take full advantage of that, you must have access to an Internet or intranet server that supports ASP. Usually, that means you use the Microsoft Internet Information Server (IIS), but you may be able to use the Microsoft Personal Web Server (PWS), which is freely available, on your own machine—see Chapter 12 for the details. I'll also take a look at using the Remote Data Service (RDS), which is now considered part of ADO, in Chapter 12. To use RDS, you'll need an Internet or intranet server that supports it, like the Microsoft IIS or PWS.

The most important resource in this book is Visual Basic itself. In the first chapter, I'll start with an overview of database programming in Visual Basic. As you'll see, there's a huge amount of material coming up, so the sooner we start, the better. It's time to turn to Chapter 1 now.