Transcript No Slide Title

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.MoveNext
datBooks.Recordset.MoveLast
datBooks.Recordset.MovePrevious
datBooks.Recordset.MoveFirst
datBooks.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 Properties
datBooks.Recordset.EOF
datBooks.Recordset.BOF
datBooks.Recordset.NoMatch
(T if on last record)
(T if on first record)
(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 = “ & txtISBN
Finds the first record of the table linked to datBooks
that has its ISBN field equal to txtISBN
datBooks.Recordset.FindNext “ISBN = “ & txtISBN
Finds 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 field
datCustBal.Recordset.FindFirst ("DateLastTransaction = #" & TargetDate & "#")
Change a field on the database without linking to a textbox
datCustBal.Recordset.Edit
datCustBal.Recordset("Balance") = 99
‘or datCustBal.Recordset “Balance” = 99
‘or datCustBal.Recordset!Balance = 99
datCustBal.Recordset.Update
‘Puts record in a buffer
‘Changes value
‘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 Sub
End If
Do While datCustomer.Recordset.NoMatch = False
lstCustomerIDs.AddItem datCustomer.Recordset!CustomerID
datCustomer.Recordset.FindNext _"LateFees = " & txtLateFeeTarget
Loop