Seminar 3

Visual Basic DataBases -

Theory and Practice

© Morris Firebaugh



Topics:



What is a DataBase?

Some Definitions:


Data Structures of Databases

To understand database concepts, it is essential to understand the data structures involved. The classical nomenclature (and Visual Basic variants) are, using a [paper filing cabinet] analogy:

A typical record might have the following fields:

 


How do databases differ from spreadsheets?


Database Functions


Advantages of Databases

 



Concept of Relational Databases

Database Models

The Power of Relational Databases

 

 

 

    1. Uses Customer File to determine that Computer City --> CUST-NO=3007
    2. Uses Order File to look up ITEM-NO --> 7639
    3. Uses Inventory File to look up Item 7639 to read QOH (Quantity On Hand) = 0
    4. Returning to the Customer File, the Moore rep referrs the Computer City customer to their sales rep Pat Sulliven, using the REP-ID link, and gives them her number.



Using Visual Basic as DBMS

Recall Visual Basic is a very "large language." The following describes the relationship between VB and Databases


Background


Nomenclature

Database management in VB involves a tremendous quantity of jargon, acronyms, and mnemonics. Its nomenclature includes:


Abstraction

VB demonstrates the power of abstraction - putting powerful methods in a box and giving it a name. For example:

With these abstract tools we can perform many of the most important database functions with absolutely NO programming.



Database Functions with the ADO Data Control

Assume we are given a database such as Biblio.mdb, a demonstration database shipped with VB 6.0. What standard database operations might we want to perform?

Remarkably, the ADO Data Control allows us to do all these and more without writing any code.

We set up the properties and methods of the ADO Data Control using the Data Form Wizard.


Example 3.1

The task is to build an interface to the Biblio.mdb database capable of navigating through the DB and performing elementary DB functions such as adding, deleting, and editing records.

  1. Open VB and start a Standard EXE project.
  2. To load the Data Form Wizard, open the Add-Ins menu, select the VB 6 Data Form Wizard, and check the Loaded/Unloaded and Load on Startup check boxes.

Now bring up the Data Form Wizard and step through its frames (windows) to select the desired properties and methods of your data form.

Step 1

Since no profile exists, click Next> and the Form Wizard will create one for us.

Step 2

Since we will be using the Access database, Biblio.mdb, click Next> to select the default value, Access.

Step 3

To select the database, we use Browse to locate Biblio.mdb which we previously copied from the Visual Basic folder to a new folder, BegDB.

Step 4

Next, we perform three important tasks: (a) give the display form the name frmFirst, (b) select Single Record as the display format, and (c) bind the database to the ADO Data Control.

Step 5

In this frame we also perform three important functions: (a) select the Publishers table from the Biblio.mdb database, (b) select the fields to display using the or buttons, and (c) select the field to sort on (Company Name). We will select all fields using the second button.

Step 6

This frame allows us to select the various functions we wish to perform on records of our table. We go with the default of Select All.

Step 7

The last frame of the Data Form Wizard allows us to name and save the profile (set of attributes and methods) we have generated in the previous six frames. We open the ellipsis box and type Seminar3a and then click Finish.

Output:

The display form generated by the Wizard is shown. It contains three parts: (a) the list of fields with captions, (b) a row of database function controls, and (c) the ADO Form Control for navigating the table. Before executing the program, we must use the Project menu --> Project Properties and set the Startup Object to frmFirst.

Run the program:

See, the first of 727 publisher records appears. We can navigate the database table using the buttons to move to the top of table or up one record, respectively, and the buttons to move down one record or to the bottom of table, respectively.

Moving to the bottom of the Publishers table displays ZILOG. We have edited the Comments field which becomes part of the record set and is automatically saved.

As the Comments indicate, if we click the Add button a blank record appears. We fill in all the fields except PubID, and upon hitting Update and Refresh, the following record appears, with PubID automatically incremented to 728.

Problems:

  1. There seems to be no "Go to Record #" feature.
  2. There seems to be no "Volume control" button on the ADO Control to assist in repid navigation of the table.
  3. There is no "Find" capability, perhaps the most useful of all database features.
  4. Trying the Delete function causes an error due to reference to Publisher in the Titles table.


Example 3.2

Let's open the Data Form Wizard again and build a new profile which will display the Publishers table as a table. Repeat Steps 1 - 7 of Example 3.1 with the following modifications:

Steps 1-3 = Identical

Step 4: Change the Form name to frmGrid and select the second line of Form Layouts to Grid (Datasheet)

Step 5 = Identical

Step 6: De-select Show Data Control (since we will navigate by Scroll bars)

Step 7: Save the profile as Seminar3b

Remember: Re-assign the startup form to frmGrid in the Project menu.

Upon Finish, the following form appears:

Run the program, scroll down to the UNICO record, and we get:

See:


Example 3.3

Even relatively complex tasks such as linking tables in relational databases are easily accomplished with the ADO Data Wizard. Recall that the Publishers.mdb database consists of four tables: [Publishers, Titles, TitleAuthor, Author]. Our task is to display individual publisher one-at-a-time, along with the Titles each publishes. We modify the steps of Example 3.1 as follows:

Steps 1-3 = Identical

Step 4: Change the Form name to frmMaster and select the second line of Form Layouts to Master/Detail

Step 5: This step now expands into three Wizard frames: (a) Master Record Source, (b) Detail Record Source, and (c) Record Source Relation shown below.

Step 6: Identical

Step 7: Save the profile as Seminar3c

Remember: Re-assign the startup form to frmMaster in the Project menu.

After clicking Finish, the Wizard presents us with the form:

And, executing gives

See:

  1. The ADO Control allows us to navigate the publishers as in Example 3.1
  2. The Titles of each publisher as attached below the PubID in scrollable list
  3. Each Publisher record is the master (parent) record
  4. The PubID on top is the unique primary key
  5. Each of the several Titles shown are the detail (child) records
  6. The PubID to the right of Title is the non-unique foreign key

Note: The ADO Data Control and associated Data Form Wizard perform an impressive variety of essential database manipulation functions without the user writing a single line of code. This is because the Wizard wrote the necessary code automatically. For instance, the code embedded in Example 3.3 frmMaster is shown here:


Option Explicit

Private Sub Form_Load() Set grdDataGrid.DataSource = datPrimaryRS.Recordset("ChildCMD").UnderlyingValue End Sub
Private Sub Form_Resize() On Error Resume Next 'This will resize the grid when the form is resized grdDataGrid.Width = Me.ScaleWidth grdDataGrid.Height = Me.ScaleHeight - grdDataGrid.Top - datPrimaryRS.Height - 30 - picButtons.Height End Sub
Private Sub Form_Unload(Cancel As Integer) Screen.MousePointer = vbDefault End Sub
Private Sub datPrimaryRS_Error(ByVal ErrorNumber As Long, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, fCancelDisplay As Boolean) 'This is where you would put error handling code 'If you want to ignore errors, comment out the next line 'If you want to trap them, add code here to handle them MsgBox "Data error event hit err:" & Description End Sub
Private Sub datPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) 'This will display the current record position for this recordset datPrimaryRS.Caption = "Record: " & CStr(datPrimaryRS.Recordset.AbsolutePosition) End Sub
Private Sub datPrimaryRS_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) 'This is where you put validation code 'This event gets called when the following actions occur Dim bCancel As Boolean Select Case adReason Case adRsnAddNew Case adRsnClose Case adRsnDelete Case adRsnFirstChange Case adRsnMove Case adRsnRequery Case adRsnResynch Case adRsnUndoAddNew Case adRsnUndoDelete Case adRsnUndoUpdate Case adRsnUpdate End Select If bCancel Then adStatus = adStatusCancel End Sub
Private Sub cmdAdd_Click() On Error GoTo AddErr datPrimaryRS.Recordset.AddNew Exit Sub AddErr: MsgBox Err.Description End Sub
Private Sub cmdDelete_Click() On Error GoTo DeleteErr With datPrimaryRS.Recordset .Delete .MoveNext If .EOF Then .MoveLast End With Exit Sub DeleteErr: MsgBox Err.Description End Sub
Private Sub cmdRefresh_Click() 'This is only needed for multi user apps On Error GoTo RefreshErr datPrimaryRS.Refresh Set grdDataGrid.DataSource = datPrimaryRS.Recordset("ChildCMD").UnderlyingValue Exit Sub RefreshErr: MsgBox Err.Description End Sub
Private Sub cmdUpdate_Click() On Error GoTo UpdateErr datPrimaryRS.Recordset.UpdateBatch adAffectAll Exit Sub UpdateErr: MsgBox Err.Description End Sub
Private Sub cmdClose_Click() Unload Me End Sub


Example 3.4

Sorting is another valuable database function. Suppose, for instance, we wanted to sort Publishers by State. To do this, we use the Output style FlexGrid.

Steps 1-3 = Identical

Step 4: Change the Form name to frmFlex and select the second line of Form Layouts to MSHFlexGrid

Step 5: For Record Source, select Publishers; select the fields State and Company Name, and sort by State

Step 6: In the Select Grid Type frame, choose Outline as shown

Step 7: In the frame Set Appearance Style, select Contemporary

Step 8: In the frame Set Column Settings, drag the Company Name column wider

Step 9: In the frame Set Application UI Options, check Allow Column Dragging . . .

Click Finish, and be sure to select frmFlex as the startup form. Then we see:

Executing the program gives:

See:


Example 3.5

How can we plot numerical data on an MS Chart? By selecting a numerical database, Nwind.mdb, in Step 3 and using the MS Chart Form Layout in Step 4

Step 5: Select Order Details, the two fields UnitPrice and Quantity, and sort on UnitPrice as shown:

Step 6: Choose X Axis, Y Axis, and agregate function as shown:

Step 7: Select 2D Line Chart Style

Step 8: Skip the checkboxes in the Appearance Properties frame

Step 9: As a Title, type "Quantity vs. Price" in the Create Chart Captions frame

Upon clicking Finish and setting the Startup Form = frmChart, we get:

Upon execution, we get:

See:


Database Design, Construction, & Analysis

Concepts in Database Design

Normalization


Tools for DB Construction

Two mainstream Microsoft DB design tools:


Example 3.6 - Using VisData to Construct a Relational Database

Let's build a Pizza database with three tables: [Customer, Inventory, and Transaction]

Step 1

Open VB, select Standard EXE, and use the Add-Ins menu to select Visual Data Manager as shown:

Step 2

The VisData application window will open. Select File-->New-->Microsoft Access-->version 7.0 MDB.

In the resulting "...Database to Create" window, type the database name, Pizza.

Step 3

The VisData window now contains two sub-windows: Database Window and SQL Statement. In the Database Window, select Properties and right click (or <SHFT> + click). Select New Table in the resulting menu.

Step 4

A Table Structure window appears in which we specify the table name, add fields, and define index lists.

Step 5

Clicking the Add Field button above, we get the following window. We name the first field CustomerID, assign its type as Long (integer), and check the AutoIncrField and Required checkboxes.

Step 6

After clicking the OK button above, we Add Field again, this time with Customer name and the default type text of size 50 characters.

Step 7

Next, in the Table Structure frame we click Add Index and assign CustomerID as a unique, primary key. We also index Customer, but as neither unique nor primary.

Step 8

Now, after verifying the correct field assignments and attributes in Table Structure, we click its Build the Table button. The Customers Table icon appears in the Database Window. Right clicking this icon opens a menu in which we select Open. A record editing window opens (with controls shown in Step 9), we click Add fill in the information, and hit Update.

Step 9

After entering four customers, we can verify our information by moving through the records with the data control of the Record Editing window.

Step 10

After completing the Customer table, we again <SHFT>+Click the Properties icon and select New Table in the resulting menu to begin the Inventory table.

Step 11

The table structure for Inventory is specified as shown, and Build the Table is clicked.

Step 12

Finally, we specify the structure for the Transaction table. Now all three tables have been designed, and the Customer table filled in.

Step 13

After the laborious task of Data Entry (or form filling), we finally reach the last record of the last table. Clicking Update here completes our Pizza database.

See:


Contents of Pizza Database

To view the contents of the three tables we have created and filled, we return to the ADO Data Control and Data Form Wizard.


Analysis - Sorting on the Pizza Database

Let's generate a report which gives us each customer and a list of the quantity, date, and price of their pizza purchases.

See:


Querying a Database - SQL

The ultimate goal of DBMSs is to be able to answer your questions quickly, easily, accurately, and through natural language queries. Great progress towards this goal has been made. Consider, for instance, the Macintosh Sherlock search engine. Ask it:

Why is the sky blue?

and it replies (if you're hooked to the WWW):

The first two (of 70) answers are links to:

http://floodplain.org/jan7.htm

http://www.physics.sfsu.edu/grad/lwilliam/sky.html


Structured Query Language (SQL)

The goal of Microsoft's data access efforts is to give users access to a wide variety of databases, data sources, and data stores. The specific design goal of ADO is to assist the user in integrating data from HTML files, e-mail, spreadsheets, and databases from all major applications.

In the meanwhile,. . .VisData provides us with a SQL Statement Window.

To understand the language in detail, an excellent resource is the SUNY Buffalo's WWW page:

Syntax of SQL


KEYWORD <data> KEYWORD <data> . . .

Additional SQL Keywords

KeyWord

Information/Data we provide

FROM Table Names
JOIN How we join multiple tables (linking)
GROUP BY Columns used for grouping agregate functions
HAVING Criteria set for aggregate functions
ORDER BY Defines how recordsets should be sorted
WHERE Conditional test(s) for selection
LIKE Substring matching often used with wildcard ( * )
BETWEEN Used with WHERE to determine range of value
IN Used to determine if field value is in a set of values
AND,OR,NOT Standard Boolean operators for building conditions
DELETE Dangerous, stand-alone command


Examples of SQL Queries

Here we use the SQL Statement window of VisData to query the databases we have built and demonstrated above.


Example 3.7

Suppose we want to examine the Description of Pizzas from the Inventory Table in order of Cost, cheapest first. How would we command the SQL?

Step 1: Open VB, open VisData, and open the Pizza.mdb database.

Step 2: Type in the following SQL command in the SQL statement window

Step 3: Click the Sort button shown in Step 4 and type in Cost

Step 4: Examine the record contents - sure enough, the fourth record now contains the $4.00 Medium Deep Pan pizza.


Example 3.8

Suppose we want to find all books on Computer Graphics published between 1992 and 2000. What SQL statement do we need?

Step 1: Open VB, open VisData, and open the Biblio database.

Step 2: Type in the following SQL command in the SQL statement window

Step 3: Executing the command gives:

See:

  1. The LIKE syntax, in conjunction with the ( * ) wildcard, identifies Titles containing the substring "Computer Graphics".
  2. The Boolean logical AND and the range syntax BETWEEN allows us to do sophisticated filtering.
  3. The compound field name Year Published must be contained in square brackets, [Year Published], since it contains a space.

Example 3.9

SQL supports the aggregate functions of COUNT, SUM, AVG, MAX, and MIN as well as statistical functions such as STDEV and VAR. Let's see how we can use these to produce an age profile of the authors in the Biblio.mdb database.

The SQL command:

produces

See:


Topics not covered