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:
Recall Visual Basic is a very "large language." The following describes the relationship between VB and Databases
Database management in VB involves a tremendous quantity of jargon, acronyms, and mnemonics. Its nomenclature includes:
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.
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.
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.
Now bring up the Data Form Wizard and step through its frames (windows) to select the desired properties and methods of your data form.
Since no profile exists, click Next> and the Form Wizard will create one for us.
Since we will be using the Access database, Biblio.mdb, click Next> to select the default value, Access.
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.
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.
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.
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.
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.
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.
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.
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:
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 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:
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:
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
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:
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 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:
Let's build a Pizza database with three tables: [Customer, Inventory, and Transaction]
Open VB, select Standard EXE, and use the Add-Ins menu to select Visual Data Manager as shown:
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.
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.
A Table Structure window appears in which we specify the table name, add fields, and define index lists.
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.
After clicking the OK button above, we Add Field again, this time with Customer name and the default type text of size 50 characters.
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.
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.
After entering four customers, we can verify our information by moving through the records with the data control of the Record Editing window.
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.
The table structure for Inventory is specified as shown, and Build the Table is clicked.
Finally, we specify the structure for the Transaction table. Now all three tables have been designed, and the Customer table filled in.
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.
To view the contents of the three tables we have created and filled, we return to the ADO Data Control and Data Form Wizard.
Let's generate a report which gives us each customer and a list of the quantity, date, and price of their pizza purchases.
and it replies (if you're hooked to the WWW):
The first two (of 70) answers are links to:
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:
KEYWORD <data> KEYWORD <data> . . .
Information/Data we provide
|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|
Here we use the SQL Statement window of VisData to query the databases we have built and demonstrated above.
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
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
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: