23 Databases

..
With over half of all VB programs involved In database management of some kind,
you have To feel obligated To understand VB 's capabilities in that area.  

Databases can come In various formats - Access, text files, Excel files, custom binary formats.

Usually, however, When one speaks of databases the reference Is To information stored In
the Access Data Format - files that end In .mdb.

With VB you can create database objects using code Or you can utilize one of several
databbound controls which can greatly simplify your access To the contents of a database.

The penalty you pay For using databases In your program Is a tremendous growth In the
program size - a minimum of 5MBytes For just putting a Single control On your form.  Also,
database operations In VB can be noticeably slow.   Finally , unlike a simple text database,
Access database files cannot be edited directly.

These penalties have To be  traded off against the simplicity With which VB allows you To
access / edit database content.

Controls
Database access In VB begins With the Data control. Set the file And table properties
And all you have To Do Is To bind fields To other controls For display Or edit.  Of VB 's
intrinsic controls, the following can be bound To the datacontrol, automatically showing
one of more fields In a database table.
- textbox
- label


As far As basic terminology goes, databases contain tables, which are collections of
Data arranged In rows And columns.  Rows are called records And columns are called
fields. Databases can also contain queries, table relationships, validation criteria And more.

A Recordset Is simple a group of records from a database.  Usually, a recordset Is
thought of As a subset of all records In a tables, although As we will see, records
can also be made up of Data from more than one table.  Records In a recordset are
In no particular order unless a user takes an action To Put them In an order.

Also, a recordset Is a temporary copy of information from the databset.  Once created, Or
edited, it must be saved into the database To be permanent.

Actually, there are 5 types of recordsets which VB can create from a database, 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.  Learning which recordset To use In a
particular application requires an understanding of the capabilities And limitations of Each .

- Table - Type Recordset
Basically a complete table from a database. You can use To add, change, Or delete records.

- Dynaset - Type Recordset
The result of an SQL 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.

ADO (ActiveX Data Objects) Is the current technology which Microsoft offers To access
databases.  It was preceded by DAO And RDO.

VB Data Controls
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
Or more tables by setting the .RecordSource Property To an SQL statement.

Then , For Data - aware controls such As a textbox, 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.  At that point the bound controls will display the information from
the chosen field of the recordset.  Edits made To the Data can be saved by either using the
Data control To move To a New record Or by executing the .Refresh method of the Data control.

There are two Data controls provided by VB
- 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 (ActiveX control)
This Is the latest version of the Data control, implemented As an ActiveX control.

Data Bound Controls
There are 7 intrinsic controls And 16 ActiveX controls which recognize databases.

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
These intrinsic controls can also be bound To fields In a recordset:
- checkbox
- combobox
- image control
- label
- listbox
- picturebox
- textbox

These ActiveX controls are also Data - aware
00 ADO Data control
01 DataComboBox
02 DataGrid
03 DataList
04 DataRepeater
05 DateTimePicker
06 DBCombo
07 DBGrid
08 DBList
09 ImageCombo
10 MaskedEdit
11 MonthView
12 MSChart
13 MSHFlexGrid
14 MSFlexGrid
16 RichTextBox