Why are Databases Better than Files?

Multiple users can all use the same database, and have access to the current, up to the minute values for the data. To get that in a file system, you’d have to rewrite the file to disk after every transaction, then reread it again before every transaction.

It’s easier to find just the information you need, rather than reading the whole file

Databases have sophisticated security features, backup features, etc.

Terminology

Data Files Data Bases

collection of files database (a collection of tables)

file table

record record

field field

key field

current record

Linking to a Database from a VB program

You use a “data control” which is another object on the VB form. This is “linked” to a specific database, and a specific table in that database.

Then you can link individual text boxes to that data control, and to particular fields in that table. (You can also just refer to particular fields on the database in code, but we’ll get to that later.)

As you change the “current record” the value displayed in the text box will change.

If you (manually or through code) change the value in the text box, it automatically changes the value on the database for that record.

The Data Control, Key Properties:

Connect Which DBMS? (i.e. ACCESS)

DatabaseName Which Database?

RecordSource Which Table?

Data Bound Controls, examples:

textbox, label, etc. (These are potential data bound controls)

Data Bound Controls, Key Properties:

DataSource Which Data Control? (Links to table)

DataField Which Field?

Some Data Control Methods for Moving:datBooks.Recordset.MoveNextdatBooks.Recordset.MoveLastdatBooks.Recordset.MovePreviousdatBooks.Recordset.MoveFirstdatBooks.Recordset.Update (use .Edit before changes)

Some Data Control Methods for Finding a Particular Record:datBooks.Recordset.FindFirst [criteria]datBooks.Recordset.FindNext [criteria]datBooks.Recordset.FindLast [criteria]datBooks.Recordset.FindPrevious [criteria]

Some Data Control PropertiesdatBooks.Recordset.EOF (T if on last record)datBooks.Recordset.BOF (T if on first record)datBooks.Recordset.NoMatch (T if no match found)

Things you might want to do with a Database

Find a particular record (i.e. account info for a particular customer)

Get the value for a particular field in a record to use in computations

Change the value for particular field in record

Add a new record

Delete an existing record

Finding a particular record, when the field you are searching for is a numeric value in the database.

datBooks.Recordset.FindFirst “ISBN = “ & txtISBNFinds the first record of the table linked to datBooks that has its ISBN field equal to txtISBN

datBooks.Recordset.FindNext “ISBN = “ & txtISBNFinds the next record of the table linked to datBooks that has its ISBN field equal to txtISBN

' FindFirst based on a numeric field -- a second example. With ‘ checking for no match. datCustBal.Recordset.FindFirst "CustomerID = " & txtCustID.Text If datCustBal.Recordset.NoMatch = True Then MsgBox "Not a current Customer" txtCustID.SetFocus Exit Sub endif

‘FindFirst based on a text field datCustBal.Recordset.FindFirst "CustomerName = '" & txtCustName.Text & "'" ‘FindFirst based on a date fielddatCustBal.Recordset.FindFirst ("DateLastTransaction = #" & TargetDate & "#")

Change a field on the database without linking to a textbox

datCustBal.Recordset.Edit ‘Puts record in a buffer

datCustBal.Recordset("Balance") = 99 ‘Changes value‘or datCustBal.Recordset “Balance” = 99‘or datCustBal.Recordset!Balance = 99

datCustBal.Recordset.Update ‘Writes buffer to DB

Adding a new record to a database

datCustBal.Recordset.addnew ‘Creates new record

‘ (then you want to fill the fields, either by having the user ‘ fill in linked textboxes or through code such as the‘ following) datCustBal.Recordset!CustomerID = Text3 datCustBal.Recordset!Balance = 9000

‘ (Then update the record to actually write to the database) datCustBal.Recordset.Update

Deleting a record from a database

datCustBal.Recordset.delete ‘deletes the current record ‘leaving NO current record

datCustBal.Recordset.MoveNext ‘Makes the current record the ‘next record.

If datCustBal.Recordset.EOF then ‘If you are “over the edge”, datCustBal.Recordset.MoveLast ‘then this brings you back. end if

A More Involved Example: Filling a List Box from a database

Find all Customer IDs with this late fee

datCustomer.Recordset.FindFirst "LateFees = " & txtLateFeeTarget

If datCustomer.Recordset.NoMatch Then MsgBox "No Customer With this late fee" Exit SubEnd If

Do While datCustomer.Recordset.NoMatch = False lstCustomerIDs.AddItem datCustomer.Recordset!CustomerID datCustomer.Recordset.FindNext _"LateFees = " & txtLateFeeTargetLoop