VBA Data Access Object

1. VBA Data Access Object

2. 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

4. 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))

5. 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

6. A Simplified DAO Model • Database • RecordSet • Fields • Relation

7. 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

8. 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.

9. 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.

10. 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”)

11. RecordSet Options • dbOpenTable • dbOpenDynaset • dbOpenSnapshot • dbOpenForwardOnly • Example: Set rs = db.OpenRecordset("customer", dbOpenForwardOnly)

12. Reading a Field in a RecordSet • Text0 = rs.Fields("cid") • Text2 = rs.Fields("cname")

13. Navigate RecordSet • Rs.MoveNext • MoveLast • MovePrevious • MoveFirst • Rs.EOF • RS.BOF

14. BOF and EOF in a Record Set BOF Record # 1 Record # 2 Record #3 EOF

15. Loop through a Recordset Do While Not Recordset.EOF ‘Perform action on data Recordset.MoveNext Loop

16. 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

17. 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

18. Other RecordSet PropertiesObject Browser