Cis 338 using dao data access objects
This presentation is the property of its rightful owner.
Sponsored Links
1 / 22

CIS 338: Using DAO (Data Access Objects) PowerPoint PPT Presentation


  • 47 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

CIS 338: Using DAO (Data Access Objects)

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Cis 338 using dao data access objects

CIS 338: Using DAO (Data Access Objects)

Dr. Ralph D. Westfall

February, 2003


Data access objects

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Updating Database: AddNew

  • adding a record

    rs[name].AddNew

    rs[name]![field1] = [value]

    rs[name]![field2] = [value]

    rs[name].Update


Updating database edit

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

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

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)


  • Login