1 / 18

VBA Data Access Object

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

kane-alston
Télécharger la présentation

VBA Data Access Object

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  17. Other RecordSet PropertiesObject Browser

More Related