VBA Data Access Object Data Access Objects DAO

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 wrk. Jet As Workspace Dim dbs. Northwind As Database Dim dbs. Sales. DB As Database Dim rs 1 As Recordset Dim rs 2 As Recordset Set wrk. Jet = Create. Workspace("", "admin", "", db. Use. Jet) Set dbs. Northwind = wrk. Jet. Open. Database("c: Northwind. mdb") Set dbs. Sales. DB = wrk. Jet. Open. Database("c: Sales. DB. mdb") Set rs 1 = dbs. Sales. DB. Open. Recordset("customer") Msg. Box (dbs. Sales. DB. Recordsets. Count) Set rs 2 = dbs. Northwind. Open. Recordset("customers") Msg. Box (rs 1. Fields(0)) Msg. Box (rs 2. 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 – Record. Set • Fields – Relation

Open a Database • Using Workspace object’s Open. Database method: – Dim db As Database – Set db=Open. Database(“path to database”) • Using Application object’s Current. DB method: – Dim db As Database – Set db = Current. Db

Database Object’s Methods • Execute: Executes an SQL statement. – dbs. Sales. DB. Execute "update customer set rating='C' where cid='c 02'" • Open. Record. Set: – Creates a new Record. Set object and appends it to the Recordsets collection.

Record. Set 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 Record. Set • Dim db As Database • Dim rs As Record. Set • Set db = Open. Database(“path to database”) • Set rs = db. Open. Record. Set(“table. Name”) • Or • Set rs= db. Open. Record. Set(“sql statement”)

Record. Set Options • • • db. Open. Table db. Open. Dynaset db. Open. Snapshot db. Open. Forward. Only Example: Set rs = db. Open. Recordset("customer", db. Open. Forward. Only)

Reading a Field in a Record. Set • Text 0 = rs. Fields("cid") • Text 2 = rs. Fields("cname")

Navigate Record. Set • Rs. Move. Next – Move. Last – Move. Previous – Move. First • 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. Move. Next Loop

Navigate Record. Set with a Loop Set db = Open. Database("c: salesdb. mdb") Set rs = db. Open. Recordset("customer") Do While Not rs. EOF List 6. Add. Item rs. Fields("cid") rs. Move. Next Loop Note: Listbox Row. Source Type property must set to Value List

Unbound Form Dim db As Database Dim rs As Recordset Private Sub Command 4_Click() rs. Move. Next If Not rs. EOF Then Me. Text 0 = rs. Fields("cid") Me. Text 2 = rs. Fields("cname") Else Msg. Box ("End of File") End If End Sub Private Sub Command 5_Click() rs. Move. Previous If Not rs. BOF Then Me. Text 0 = rs. Fields("cid") Me. Text 2 = rs. Fields("cname") Else Msg. Box ("BOF") End If End Sub Private Sub Form_Load() Set db = Current. Db Set rs = db. Open. Recordset("select cid, cname from customer") Me. Text 0 = rs. Fields("cid") Me. Text 2 = rs. Fields("cname") End Sub

Other Record. Set Properties Object Browser