What is a Database?
While there are a lot of concepts you need to know to become an expert at working with databases, here are three definitions with which you should start:

• Database
  "A set of data related to a particular topic or purpose. A database contains tables and can also contain queries and table relationships, as well as table and column validation criteria."
• Table
  "A table is a collection of data, arranged in rows and columns. For example, you might have a table for author information called authors. Each column would contain a certain type of information, such as the author's last name. Each row would contain all the information about a specific author: first name, last name, address, and so on."
• Recordset
  "A logical set of records. where Microsoft defines a record as "A set of related data about a person, place, event, or some other item. Table data is stored in records (rows) in the database. Each record is composed of a set of related fields (columns) - each field defining one attribute of information for the record. Taken together, a record defines one specific unit of retrievable information in a database."

In case you didn't catch the word "logical" in the definition of a recordset let me point it out. It is very important! In dealing with databases, VB creates a temporary object called a recordset, which contains the data (rows and columns) from one or more tables in the database. The recordset is not the database, it is just a working copy of some or part of the database tables.

Again, pay attention to the "some or part" comment. A recordset does not necessarily contain the entire contents of a table within a database file. You can use SQL queries (discussed elsewhere in my tutorial) to limit the recordset to selected records. Finally, you should understand that the records in a recordset are in no particular order unless you write code to order them.

In your VB code you can manipulate the contents of the recordset but the actual database file will not be changed until you or the user take action to save the changes!

One final set of definitions which you should learn has to do with the types of recordsets which VB can create for you. There are actually 5 types of recordsets you can create, having to do with where the data can come from, how you can navigate through the records, and whether you can edit the data in the recordset.

The type of recordset you use is very critical to get good performance from your database application. Some recordset types take much fewer resources than others. Also, some recordset types result in a 10X faster performance. The default values VB uses to open recordsets are often not the best choice! Learn the differences and make a specific selection in your own programs.

Here are the five types of recordsets:

• Table-type Recordset
  Basically a complete table from a database. You can use to add, change, or delete records. This is the simplest in concept since it matches a complete table in the database, but is not necessarily the best choice in many applications.
• Dynaset-type Recordset
  The result of a query that can have updatable records. You can use to add, change, or delete records. It can contain fields from one or more tables in a database. Provides worse performance than a Table-type recordset.
• Snapshot-type Recordset
  A read-only set of records that you can use to find data or generate reports. Can contain fields from one or more tables in a database but can't be updated. Uses a minimum of resources and provides fast performance.
• Forward-only-type Recordset
  Identical to a snapshot except that no cursor is provided. You can only scroll forward through records. This improves performance in situations where you only need to make a single pass through a recordset.
• Dynamic-type Recordset
  A query result set from one or more base tables in which you can add, change, or delete records from a row-returning query. Further, records other users add, delete, or edit in the base tables also appear in your Recordset.

While they all have their place, it's my experience that the first three are the most useful.

Accessing Databases
Remember that there are a variety of database products on the market, and that many vendors have their own proprietary formats in which the data is stored. In the case of Microsoft's Access database program, the method of accessing the database is through software which Microsoft calls its Jet Database Engine. Other vendors have their own access methods.

Many companies, including Microsoft, have provided their databases with the ability to be read by software which uses a standard database manipulation method known as ODBC (Open Database Connectivity). While a programmer can choose to let the VB database-aware controls handle the details of accessing or manipulating databases (recordsets), it is also possible to use code to create and edit databases.

DAO (Data Access Object)
The first method VB offered for using ODBC to create/edit databases was DAO. It allowed the programmer to create a variety of objects in code, objects whose properties and methods could be used by programmers to create/edit the databases. This worked for Microsoft Access databases as well as any other ODBC-compatible database.

Microsoft also offered a second data access interface to ODBC which had some improved features over DAO, but which also lost some of the more general capabilities of DAO. This approach, called RDO (Remote Data Objects), primarily targeted the more complex aspects of stored procedures and result sets. It is generally not used by most programmers.

ADO (ActiveX Data Objects)
Microsoft is in the process of replacing the ADO/RDO features with the newer ADO. ADO has a flatter model (meaning fewer objects) and is intended to be simpler to use. In it's HELP files, Microsoft states that "ADO definitely represents the future of data access technology"

Personally, I would like the choice of database access technology to be transparent to me. However it's obvious that the Microsoft approach is still under flux and that programmer's will need to be aware of multiple approaches for some time. This holds true because the ADO features have just been introduced and have not yet matched the DAO features of prior versions of VB.

For beginners, or any programmer who simply wants to put a data control on a form and then display database information in other controls, the choice of ADO/DAO/RDO is not critical because the controls handle it for you, The advantage in manipulating databases in code is that you have a far greater degree of control over what actions are performed against the databases.

VB Data Control
VB provides two controls which makes the link to the database file and which creates the recordset that is exposed to the rest of the controls in your application. The two are identical in concept but differ in the flexibilty they offer to the programmer. I'll discuss the common features first and then cover some of the differences.

The concept of a data control is pretty simple. You set the .DataBaseName property to tell the data control which database file to read. Then you set a .RecordSource property to tell it which table within the file to make available as a recordset to other controls. As I've mentioned earlier, you can also create recordsets which are combinations of one of more tables by setting the .RecordSource property to an SQL statement.

For any controls which support databases, you set the .DataSource property to the data control and the .DataField property to the specific field within the table/recordset that you want bound to the control.

That's it! At that point the bound controls will display the information from the chosen field of the recordset. If you make changes to the data then the database can be changed to reflect the changes by either using the data control to move to a new record or by executing the .Refresh method of the data control.

Ok, perhaps I simplify things a bit too much because there really are other properties which a beginner must also be familiar with. But, it really can be pretty simple. You can create entire database applications with essentially no coding, just the setting of control properties.

If you didn't read my tutorial on SQL statements, then you've missed one of the most useful database capabilities that VB has to offer. I won't repeat all of that section again, but in a nutshell, you can set the .RecordSource to a command which tells the data control to create a recordset that is a selected subset of the fields in one or more tables. And there is lot more that you can do with SQL to reduce the amount of coding that you have to write yourself. Every VB programmer should be familiar with the use of SQL to create/manipulate recordsets. SQL can even be used to make mass changes to a recordset, including search and replace actions. Take the time to read my SQL tutorial section and to review the VB documentation on SQL. You'll use it the rest of your career!

Finally, we get to the actual list of the two data controls I mentioned.

• Data control (intrinsic version)
  This is the original, intrinsic version of the control. It supports the JET database engine and can satisfy most beginners' needs.
• ADO Data control
  This is the latest version of the data control, implemented as an ActiveX control.

In line with keeping your distribution files to a minimum, I suggest you use the intrinsic control whenever possible. I haven't seen enough information about the ADO Data Control to convince me that it's worth incorporating a separate ActiveX control into my applications.

Data Bound Controls
Once you have the recordset available from the data control, you'll find VB to have a rich variety of controls which can access the recordset. There are 7 intrinsic controls and 16 ActiveX controls which you have available to use. Each has its place and over time you'll find yourself using most of them.

With the proliferation of controls, some of which are similar, Microsoft has created some confusion. In particular, there are multiple versions of the combobox, list, and grid controls. Let's try to address them first and then we'll get into the rest of the databound controls. If you really don't want to worry about selecting between multiple options of the controls, simply go with the latest version (DataList, DataCombo, and DataGrid) and don't worry about the nuances between the various versions of the controls.

ComboBox
Here are the three versions that are available with VB, and some comments on how to decide which one to use.

• ComboBox
  This is the original, intrinsic, version of the control. Use it whenever possible to keep down the size of your application.
• DataComboBox
  This is Microsoft's most recent rendition of a combo control.
• DBCombo
  Left over from VB5, you can forego using this one in favor of the newer DataCombo control.

List
Here are the three versions that are available with VB, and some comments on how to decide which one to use.

• ListBox
  This is the original, intrinsic, version of the control.
• DataList
  This is Microsoft's most recent rendition of a list control.
• DBList
  Left over from VB5, you can forego using this one in favor of the newer DataList control.

Grid
There are actually four versions of a grid control that come with VB, but only three of them can be databound. Here are the three versions and some comments on how to decide which one to use.

• DBGrid
  The olders version that came with VB5. You'll have to find it on the VB CDROM because it doesn't get automatically installed.
• FlexGrid
  Also a VB5 version of the grid control. It does not support editing of the bound data.
• Heirarchical FlexGrid
  Newest version of the VB5 FlexGrid and still does not support editing of the bound data.

Other Data-Aware Controls

There are 8 intrinsic controls which can be bound to fields in a recordset:
• checkbox
  
• combobox
  
• data control
  
• image control
  
• label
  
• listbox
  
• picturebox
  
• textbox
  

There are also 16 ActiveX controls which can be bound to fields/recordsets:
• ADO Data control
  
• DataComboBox
  
• DataGrid
  
• DataList
  
• DataRepeater
  
• DateTimePicker
  
• DBCombo
  
• DBGrid
  
• DBList
  
• ImageCombo
  
• MaskedEdit
  
• MonthView
  
• MSChart
  
• MSHFlexGrid
  
• MSFlexGrid
  
• RichTextBox
  

Database Reporting
In the tutorial section on printing I lavished praise on how VB has made printing much easier. In the area of creating and printing reports, VB6 has taken even greater steps to simplify the programmers coding burden. There are now two options available to you which can greatly simplify the amount of coding it takes to print data that resides in a database.

In a prior version of VB a free version of a program called "Crystal Reports" was provided. It included an OCX you could distribute with your applications. A Pro Version was available commercially.

The magic of Crystal Reports was that you could create the report visually, in an IDE similar in concept to the VB IDE. Once the report was created you could use code to tie a database to it and then use code within your application to print (or preview) the report! The version that came with VB wasn't as flexible as the Pro version but it meant that you could create a professional-looking print report literally in a matter of minutes. Making changes under this concept is an order of magnitude easier than it would be to make changes in code which manually created the equivalent reports. I consider it an absolute winner that VB programmers should take advantage of.

With VB6, Microsoft brought out their own version of a report writer. They include Crystal Reports on the VB CDROM but it is not automatically loaded on your PC when VB is installed. You can install it at any time by heading over to the \common\tools\vb\crysrept directory of the VB CDROM and running the installation program.

I've yet to use the built-in report writer. Once I do, I'll update this section to provide a comparison.