Transcript VBA Data Access Models

VBA Data Access Object
Data Access Objects DAO
• With DAO we can:
– Run queries
– Update values in database tables
– Create structure of databases
• Tables, relationship, etc.
• Mainly used for Access databases
Workspace Example
Dim wrkJet As Workspace
Dim dbsNorthwind As Database
Dim dbsSalesDB As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsNorthwind = wrkJet.OpenDatabase("c:\Northwind.mdb")
Set dbsSalesDB = wrkJet.OpenDatabase("c:\SalesDB.mdb")
Set rs1 = dbsSalesDB.OpenRecordset("customer")
MsgBox (dbsSalesDB.Recordsets.Count)
Set rs2 = dbsNorthwind.OpenRecordset("customers")
MsgBox (rs1.Fields(0))
MsgBox (rs2.Fields(0))
DAO Programming
• Retrieving data by running a select query
– Creating recordset
• Iterating through the records in a
recordset, one record at a time.
• Running an action query
– Update
– Delete
– Insert
A Simplified DAO Model
• Database
– RecordSet
• Fields
– Relation
Open a Database
• Using Workspace object’s OpenDatabase
method:
– Dim db As Database
– Set db=OpenDatabase(“path to database”)
• Using Application object’s CurrentDB
method:
– Dim db As Database
– Set db = CurrentDb
Database Object’s Methods
• Execute:Executes an SQL statement.
– dbsSalesDB.Execute "update customer set rating='C'
where cid='c02'"
• OpenRecordSet:
– Creates a new RecordSet object and appends
it to the Recordsets collection.
RecordSet Type
• Table:Connected to a table directly
– Editable, and fast because table can be indexed
– Single table
• Dynaset: Representing a set of references to the
result of a query. The query can retrieve data
from multiple tables.
– Updatable
• Snapshot: Return a copy of data.
– Not updatable
• Forward-only: A snapshot that can only move
forward.
Creating a RecordSet
• Dim db As Database
• Dim rs As RecordSet
• Set db = OpenDatabase(“path to
database”)
• Set rs = db.OpenRecordSet(“tableName”)
• Or
• Set rs= db.OpenRecordSet(“sql
statement”)
RecordSet Options
•
•
•
•
•
dbOpenTable
dbOpenDynaset
dbOpenSnapshot
dbOpenForwardOnly
Example:
Set rs = db.OpenRecordset("customer", dbOpenForwardOnly)
Reading a Field in a RecordSet
• Text0 = rs.Fields("cid")
• Text2 = rs.Fields("cname")
Navigate RecordSet
• Rs.MoveNext
– MoveLast
– MovePrevious
– MoveFirst
• Rs.EOF
• RS.BOF
BOF and EOF in a Record Set
BOF
Record # 1
Record # 2
Record #3
EOF
Loop through a Recordset
Do While Not Recordset.EOF
‘Perform action on data
Recordset.MoveNext
Loop
Navigate RecordSet with a Loop
Set db = OpenDatabase("c:\salesdb.mdb")
Set rs = db.OpenRecordset("customer")
Do While Not rs.EOF
List6.AddItem rs.Fields("cid")
rs.MoveNext
Loop
Note: Listbox RowSource Type property must set to Value List
Unbound Form
Dim db As Database
Dim rs As Recordset
Private Sub Command4_Click()
rs.MoveNext
If Not rs.EOF Then
Me.Text0 = rs.Fields("cid")
Me.Text2 = rs.Fields("cname")
Else
MsgBox ("End of File")
End If
End Sub
Private Sub Command5_Click()
rs.MovePrevious
If Not rs.BOF Then
Me.Text0 = rs.Fields("cid")
Me.Text2 = rs.Fields("cname")
Else
MsgBox ("BOF")
End If
End Sub
Private Sub Form_Load()
Set db = CurrentDb
Set rs = db.OpenRecordset("select cid, cname from customer")
Me.Text0 = rs.Fields("cid")
Me.Text2 = rs.Fields("cname")
End Sub
Other RecordSet Properties
Object Browser