cis 338 using dao data access objects
Download
Skip this Video
Download Presentation
CIS 338: Using DAO (Data Access Objects)

Loading in 2 Seconds...

play fullscreen
1 / 22

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


  • 81 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' CIS 338: Using DAO (Data Access Objects)' - marli


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)
ad