CIS 338: Using DAO (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)

CIS 338: Using DAO (Data Access Objects)