CVEV 118/698 Database Programming

1
CVEV 118/698Database Programming

• Lecture 1

Prof. Mounir Mabsout Elsa Sulukdjian Walid El
Asmar
2
Introduction

• MS Access is a very particular commercial
  application for it is provided with two
  programming tools Macros VBA.
• Macros can be quickly and easily developed even
  with a primitive programming knowledge.
• However, their functionality is limited. For
  complex database projects, VBA should be used.

3
VBA/Macros

• Both Macros and VBA use the same set of
  predefined instructions for common actions, I.e.
  opening, closing, importing, etc. objects.
• Actions are to Macros what Methods are to
  VBA.
• The two automating tools are not totally
  independent, you can run macros from a VBA
  application and vice-versa.
• Yet, each has a different way of acting on
  database object.

4
VBA/Macros (Contd)

• Macros act directly at the level of the object
  except for the SetValue action that allows
  modifying the value of some object properties.
• The DoCmd can be used in VBA to call a Macro, and
  vice versa.

Macros
Object
Actions SetValue
Limited approach
Properties
Methods
DoCmd Command
Object enabled approach
VBA
5
VBA/Macros (Contd)

• There are approximately 50 macro actions in
  Access.
• The actions that can be performed are predefined,
  and limited in number.
• Examples
• Open Report
• Open Form

6
Data Access Components

• When installing Access, you actually install two
  major components the Access Application Layer
  and the Jet Database Engine.
• The Access Application is the part you interact
  with to display the database objects.
• The Jet Database Engine is the part where you
  manage the data in the database.

7
Data Access Components (Contd)

• The Access Application Layer the Jet Database
  Engine communicate with each other using Data
  Access Languages.
• Data Access Languages
• In Access, there are two Data Access Languages
  Structured Query Language (SQL) and ActiveX Data
  Objects (ADO).
• Data Access Languages work as links that access,
  retrieve, manipulate, etc. data.

8
The Object Model

• Remember that VBA is Object oriented programming.
• Objects have Properties and Methods to modify
  those properties.
• The object model is a hierarchical organization
  of objects, groups of similar objects and
  relations.
• Access is organized into 2 separate hierarchies,
  one stemming from the Access Application Layer
  and the other from the Jet Database Engine.
• Thus, Data Access Languages will function as
  links between those 2 trees.

9
Access Application Hierarchy
Application
Screen
DoCmd
References
Modules
Reports
Forms
Module
Module
Controls
Controls
10
Jet Data Base Engine Hierarchy
DBEngine
Errors
Workspaces
Users
Groups
Databases
TableDefs
QueryDefs
Recordsets
Relations
Containers
Documents
Fields
Fields
Fields
Fields
Indexes
11
Referring to Objects by Name

• One way to refer to an object is to trace its
  position in the object model, traversing the
  hierarchy from the top downward.
• The exclamation point operator (!), or bang, is
  used to step from a collection to one of its
  members.
• The dot operator (.) to step from an object to
  one of its collections.

12
Example

• Accession a form in the Access Application model
• Application.Forms!NameOfForm
• Dont forget to enclose in square brackets if the
  name contains spaces
• Application.Forms!Name Of Form
• Note that you can decrease the length of a
  reference by using defaults I.e. Access assumes
  that you are in Access when you refeer to
  objects
• Forms!NameOfForm
• Reports!NameOfReport
• A dot is appended at the end of this reference
  protocol to access an object property.

13
Recordset Object

• A Recordset object represents the entire set of
  records from a base table or the results of an
  executed command.
• At any time, the Recordset object refers to only
  a single record within the set as the current
  record.
• The Recordset Object represents the entire set of
  records from a Table or Query

14
Recordset Object (Contd)

• Examples
• Dim rst As New ADODB.Recordset
• set rst.ActiveConnection conn
• rst.Open "Engineer"
• - OR -
• rst.Open "SELECT ENFName, ENLName, ENDOB FROM
  Engineer"
• With a recordset you can access the whole set of
  rows in the table or query.
• However, at a given moment the recordset will
  have a single row as the current row, and data
  can be read from and written only to this row.

15
Recordset Object (Contd)

• Navigation between rows is performed through a
  set of functions
• MoveFirst
• MoveLast
• MoveNext
• MovePrevious
• Data is read and written through the Fields
  collection. It acts on the current record.
• Example
• rst.Fields("ENFName") "Karim
• rst.MoveNext
• Debug.Print rst.Fields("ENDOB")

16
Whats Next

• Less concepts, more methods!