ISM 3230 Introduction to Programming

 Chapter 11: Database Files

Visual Basic and Database files

• Database formats supported
  • dBASE, Excel, FoxPro, Lotus
  • Paradox, text files
  • With ODBC: SQL server, Oracle, DB2

Database Terminology

• File: collection of tables
• Table: consists of rows and columns
• Row also known as a record
• Field (column): data element of a table
• Key field (primary key): unique identifier for row
• Current Record: available record

Below is a portion of a Microsoft Access table with table elements labeled.
Table is but one of several tables that comprise the database.
Database tables, queries, forms, and other elements are stored in a single Access File (for example, the file named RnRBooks.mdb)

Creating DBs for use by VB

• Cannot use VB Standard Edition or Learning Edition to create new database
• You can run the Visual Data Manager add-in that comes with VB
  • Select Visual Data Manager from the Add-Ins menu
  • The VisData application window opens

Using the data control

• Data control is placed on form to allow access to a single database.

• Two step process:
  • place data control on the form, setting the properties to link it to a database file and table.
  • create the actual controls on the form to display the database data.
• Bound Controls are controls that are tied to database fields and are generally not available for free-form input. They are called data-bound controls, because they automatically display database fields. (Data aware controls is a synonym.)

Data control:

• Data control’s name has the prefix dat
• Set the data control Connect property to the type of database file
• Set the data control’s DatabaseName property to the path/filename of the associated database file
• Set the data control’s RecordSource property to any table or query in the database
• Data-Bound controls on the form:
• Set the bound control’s DataSource property to point to the data control
• set the bound control’s DataField property to the database field it is to display

• Set three properties on the Data Control:
  • Connect -database type (Access)
  • DatabaseName -name/location of DB
  • RecordSource -table/query (view) name
• Set ReadOnly property to True if you want to prevent updates to database
• Next, you can set up data-bound controls

Data-Bound Control Properties

• After placing text boxes, option buttons, etc. on the form, you can bind each control to a column (field) of the table or query whose name is in the RecordSource property.
• Set DataSource - point to Data Control
• Set DataField - specify Table field
• Tie form’s bound-control to database field by setting the DataField property to the appropriate database table field name:

Viewing a Database File

• Design & create the form
• Set the properties for the data control
• Set the properties for the data-bound controls
• Write the code
• Run the project

Navigating the database in code

• Data control provides navigation controls
• You can hide the data control and provide record movement in code
• Recordset object is defined by RecordSource property of Data control
• Tip: use App.Path to set DatabaseName property
  • A problem with setting the Data Control’s DatabaseName property at design time is that you don’t know where the database will be located when the user runs it.
  • It is handy to set the DatabaseName at run time so that it corresponds to the disk drive and folder from where the project was loaded
  • The path—disk drive and folders—are saved in the Path property of the App object (App.Path)

The following code helps to establish a default location for the database--the same location as the application:

Private Sub Form_Load()

'Set the file path to the current location of the project

If Len(App.Path) = 3 Then

datBooks.DatabaseName = App.Path & "RnrBooks.mdb"

Else

datBooks.DatabaseName = App.Path & "\RnrBooks.mdb"

End If

End Sub

• Why does the code test for the length of the path name?
• You can also use the VB common dialog box to select the database file.

• Using the MoveNext, MovePrevious, MoveFirst, and MoveLast methods
• Provide Recordset methods to navigate DB
• Recordset has two handy properties called BOF and EOF
  • BOF set when just before first record or record set is empty
  • EOF set when just after last record

   

RecordSet Methods

• Record movement in code via methods of the Recordset:
• datBooks.Recordset.MoveNext ‘go to next record
• datBooks.Recordset.MoveLast ‘go to Last record
• datBooks.Recordset.MovePrevious ‘go to previous record
• datBooks.Recordset.MoveFirst ‘go to first record

BOF and EOF are properties of the Recordset object.

• The BOF property is True when the record pointer is before the first record in the recordset; it is also True if the Recordset is empty.
• The EOF property is True when the record pointer moves beyond the last record and when the Recordset is empty.
• You can use the "wrap around" method where the first record is displayed if the user clicks MoveNext at the end of the database.
• Similarly, you can "wrap around" by moving to the last record when the user clicks MovePrevious at the front of the database.

Using List & Combo Boxes as Data-Bound Controls

• List boxes and combo boxes are data aware
• Set up a lookup table for a field by using a combo box
• You can bind the combo box to a database field and give the List property initial values

Lookup Table & Navigation

• Modify the user interface
• Change the properties
• Write the code

Updating a Database File

• Recordset object has an Update method that updates the underlying database
• Update occurs automatically when a navigation button is clicked
• The AddNew method adds records to DB
• All bound controls are cleared
• Save with Update method or move off the record

Adding and Updating Records

• Add new records with AddNew method:
• datBooks.Recordset.AddNew
• Update an altered record with the Update method:
• datBooks.Recordset.Update
• An error that can occur when adding a record is that the record’s key is 1) empty or 2) duplicated (found in another record already).
• The DataChanged property is set to True whenever a bound control’s contents are changed by the user.
• Before Ending a program, be sure to check the DataChanged property of every bound control to see if the user made changes.
• If data has changed in the bound controls that has not been posted to the underlying database, then ending the program will cause those changes to not be posted unless you check the DataChanged property.

Deleting Records

• Delete method of the Recordset object deletes existing record
• After a delete operation, move to another record because deleted record is no longer valid

Example Delete

• takes into account all possibilities, even when the deleted record is the last and only record.

• The previous code handles all delete situations: Normal, EOF, and BOF

Preventing Errors

• It is bad form for users to encounter errors
• Error Trapping intercepts errors before users see them
• Limit user’s action when an operation starts
• CancelUpdate method cancels record alteration (add or update) and restores a record to its original form

Cancel

• Protecting the add operation:
• Clear the bound controls.
• Set focus on first text box.
• Disable navigation buttons and delete button.
• Enable Save button.
• Change caption of the Add button to Cancel.
• Sharing the functions of a command button is useful when you want a button to perform one of several actions depending on the situation.
• For instance, the Add button can morph into the Cancel button once an add-record operation is begun.
• To accomplish this, you do two things:
• Change the button's caption property
• Test the button's caption property to determine what to do in response to a click event
• The CancelUpdate method cancels current record alteration procedure and restores the record to its original form.

Hands on Programming Example

Chapter Summary:

OBJECTS		PROPERTIES		METHODS
Data control		BOF		AddNew
RecordSet		Connect		Delete
		DatabaseName		MoveNext
		DataField		MoveLast
		DataSource		MovePrevious
		EOF		MoveFirst
		RecordSource		Update