Transcript CIS 338: Using Data Access Objects

CIS 338: Using DAO
(Data Access Objects)
Dr. Ralph D. Westfall
February, 2003
Data Access Objects
VB objects that can be used in program
code to work with databases


can set properties in code
can use methods "
"
can do more than with Data control


e.g., input validation based on business
rules
handling data from automated sources
Starting a DAO Project
start a new VB standard EXE project
select Project>References
click Microsoft DAO 3.6 Object Library

use 2.5/3.5 if will work with older
databases
Starting a DAO Project - 2
add code that will set up the database

above Form_Load code (General):
Dim db[Name] As Database

in Form_Load code:
Dim s[dbloc] As String
s[dbloc] = App.Path & "\[file].mdb"
'or use actual physical path
Set db[Name] = _
OpenDatabase(s[dbloc])
Recordset Type - Table
gives direct access to table/all records
can change sort order
 can see changes by other users
 above Form_Load code:
Dim rs[name] As Recordset

in Form_Load code:
Set rs[name] = _
db[name].OpenRecordset("[table]", _
dbOpenTable)
'sample database

Recordset Type - Dynaset
fields/records from one table
Set rs[name] = db[name].OpenRecordset_
("[table]", dbOpenDynaset)
can use SQL to get data from multiple
tables
Dim sSQL as String
sSQl = "SELECT … FROM … WHERE …"
Set rs[name] = db[name].OpenRecordset_
(sSQL, dbOpenDynaset)
Recordset Type - Snapshot
gets "read only" copies from one or
more tables



faster processing
uses more memory
can't change sort order
Set rs[name] = db[name].OpenRecordset_
("[table]", dbOpenSnapshot)
'table
Set rs[name] = db[name].OpenRecordset_
(sSQL, dbOpenSnapshot)
'SQL string
Recordset Syntax
Set rs[name] = [object].OpenRecordset _
(source, type, options, lockedits)
 object: usually a database
 source (required): table, query in database,
SQL string
 optional parameters:
 type: must be dbOpenTable for table source, can
be dbOpenDynaset, etc. if from query or SQL
 options: dbReadOnly, dbForwardOnly, etc.
Putting DAO Data in Form
add controls (textboxes) to form
write code to set property of control to
field in database

textboxes: set Text property
txt[name].Text = _
rs[name].Fields("[name]") 'space prob
txt[name].Text = rs[name]![name]

could set label Caption similarly 'Notes
Using Record Pointer
record pointer identifies specific record
positioning





Move methods – moves pointer
Find and Seek methods – goes to specific
record
Bookmark property – saves location
AbsolutePosition – go to record #
PercentPosition – go % to of total items
Using Record Pointer - 2
Move
MoveFirst, MoveNext, MovePrevious,
MoveLast
 Move n
e.g. Move 2, Move –35
(negative)
rs[name].MoveFirst
rs[name].Move 14
'Notes

Using Record Pointer - 3
Find

FindFirst, FindNext, FindPrevious, FindLast
rs[name].FindFirst [criteria]
criteria = string like in a SQL WHERE clause
sCriteria = "State='CA'"
rsCustomers.FindFirst sCriteria

rs[name].NoMatch indicates if not found
If rsCustomers.NoMatch = True
'not found

Using Record Pointer - 4
Bookmark identifies a location (like a
record number, but stored as string)
sVariable = rs[name].Bookmark

stores location of current record
rs[name].Bookmark = sVariable

moves to bookmarked record
rs[name].PercentPosition = 10
Conditions in Strings
need to get single quotes around
strings in an SQL query or Find criteria
sCriteria = "[field] = ' " & [string] & " ' "
 note ' " before string, " ' after
sCriteria = "Title = '" & sTitle & "'"
 If sTitle = "Jaws" this evaluates to
sCriteria = "Title like 'Jaws'"
'see p. 580
Conditions in Strings - 2
can store a single quote as a string
variable to set up SQL strings as criteria
SQ = "'"
'single quote in double quotes
sCriteria = "Title = " & SQ & sTitle & SQ
'also mentioned on p. 580
Using Indexes
indexes control sort order
different indexes for different sorts
 indexes must be created before recordset
 can only use indexes with table recordsets,
not dynasets or snapshots
rs[name].Index = "[name of index]"
'existing index
'creating a new index (MSDN site)

Filters
work with dynasets, snapshots, not
tables
rs[name].Filter = [criterion]
criterion = string like condition in a SQL
WHERE clause, but without the word
WHERE
sCriterion = "Zip='90806'"
rsStudents.Filter = sCriterion

Sorting
works with dynasets and snapshots, but
not with tables
rs[name].Sort = [field(s)]

sorts according to "collating sequence"
 in ASCII: spaces first, followed by some
quotation marks, numbers, capital letters,
more quotation marks, then lower case
rsCustomer.Sort = "City, Zip"
Updating Database: AddNew
adding a record
rs[name].AddNew
rs[name]![field1] = [value]
rs[name]![field2] = [value]
rs[name].Update
Updating Database: Edit
editing a record

values replace data in current record
rs[name].Edit
rs[name]![field1] = [value]
rs[name]![field2] = [value]
rs[name].Update
'very similar to .Add in DAO
Updating Database: Delete
deleting a record


current record is deleted
can use record pointer positioning
commands (like Find) to identify record to
delete
rs[name].Delete

before other actions, need to be sure
record is not on EOF or will not be on BOF
Transaction Processing
used when transaction (group of actions) all
need to happen to database at same time

all or none basis
BeginTrans – starts transaction
ws[name].BeginTrans
'ws = workspace object
RollBack – removes all changes since
BeginTrans
CommitTrans – permanently saves (can't be
rolled back afterwards)