Transcript VB Lecture 1 - American University of Beirut

CVEV 118/698
Database
Programming
Lecture 1
Prof. Mounir Mabsout
Elsa Sulukdjian
Walid El Asmar
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.
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.
VBA/Macros (Cont’d)


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
Actions
‘SetValue’
Object
Limited
approach
Properties
‘DoCmd’
Command
VBA
Object enabled
approach
Methods
VBA/Macros (Cont’d)



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
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.
Data Access Components (Cont’d)




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.
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.
Access Application Hierarchy
Application
Forms
Controls
Modules
Module
Reports
Controls
References
Module
DoCmd
Screen
Jet Data Base Engine Hierarchy
DBEngine
Workspaces
Users
Indexes
Errors
Groups
Databases
TableDefs
QueryDefs
Recordsets
Relations
Containers
Fields
Fields
Fields
Fields
Documents
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.
Example




Accession a form in the Access Application model:
Application.Forms!NameOfForm
Don’t 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.
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
Recordset Object (Cont’d)

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.
Recordset Object (Cont’d)



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")
What’s Next

Less concepts, more methods!