Transcript Chapter 5

1
Chapter 5
Using Objects and
Properties
5
Chapter Objectives
• Define and explain the roles of objects and
properties in an Access database
• Describe the difference between Microsoft Access
objects, Data Access Objects (DAO), and ActiveX
Data Objects (ADO)
• Write dot and bang notation that retrieves and
updates object properties in a macro and module
• Create a query with a criterion that references a
control on a form
5
Chapter Objectives
• Modify the properties of a form through a
macro or VBA statement
• Modify the properties of a linked table
• Create recordsets in a VBA procedure
• Find and describe properties related to
many objects, including forms, reports,
tables, commands, controls, fields,
databases, and recordsets
5
The World According to
Objects
• Each object has properties
 Data items that describe the objects
• Object characteristics:
 Can contain other objects
 Have methods
 Capabilities or procedures that the object can
perform
 Related objects can be grouped
5
The World According to
Objects
• A group of objects, called a subclass, is a
subset of another group of objects, called a
superclass
• Class inheritance
 Process when a new subclass is placed inside a
superclass, objects within that subclass inherit
the properties of the superclass
5
The World According to
Objects
• Object-oriented development environment
 Uses objects that have the features just
described
• Object-based development environment
 Supports object properties, object methods,
collections, and objects that contain other
objects
5
Objects and Collections in
Access
• Three major categories of objects exist:
 Microsoft Access objects
 Created and maintained by Access programming
environment
 Primarily relate to the user interface and modules
 DAO objects
 Created and managed by the Jet database engine
 Primarily involve objects related to data storage and
retrieval
 TableDef and QueryDef objects are examples of DAO
objects
5
Objects and Collections in
Access
• Three major categories of objects exist
(continued):
 ADO objects
 Similar to DAO objects
 Table object replaces the TableDef object used in
DAO and the Command object replaces the
QueryDef DAO object
5
Objects and Collections in
Access
• Collection
 An object that contains related objects
• Tables collection
 Contains definitions of all Table objects
• Forms collection
 Contains all open forms; all forms that are
loaded
5
Objects and Collections in
Access
• Each TableDef object owns a Fields collection
that in turn contains the Fields objects in the
table
• Each Form object owns a Controls collection
that contains all of the controls in the form
• Count property of a collection
 Indicates the number of objects in the collection
5
Objects and Collections in
Access
Figure 5-1 Some of the relationships among Microsoft Access objects
5
Objects and Collections in
Access
Figure 5-2
Relationships
among DAO
objects
5
Objects and Collections in
Access
Figure 5-3
Relationships
among ADO
objects
5
Objects and Properties in
Access
• Dot notation
 Syntax used by VBA, macros, and queries
 Uses the dot (.) operator to indicate that what
follows is an item defined by Microsoft Access
• Bang notation
 Notation using the exclamation point (!)
 Can be used in place of dot notation when the
collection referenced by an object is the default
collection
5
Objects and Collections in
Access
• Keywords can be used to shorten object
property paths
 VBA can use the word Me inside a form or
report class module to refer to the form or
report that contains the module
 A form or report class module can use the word
Parent to refer to a form or report that contains
another form or report
5
Changing Property Values
• VBA can change the value of a property by
using the assignment statement in a VBA
procedure
• VBA supports several data types:
 Integer is a relatively small integer
 Long is a larger integer
 Single is a real number
5
Changing Property Values
• VBA supports several data types (cont.):
 Double is a real number that offers twice as
much storage space and precision as a Single
 String is a series of characters or numbers
 Boolean is True or False
 Variant can contain anything
5
Changing Property Values
Table 5-1
Microsoft
Access
objects
5
Changing Property Values
• The SetValue action requires two action
arguments:
 Item argument
 Contains the dot or bang notation that identifies the
property
 Expression argument
 Contains the new value
5
Changing Property Values
Figure 5-5
Macro
action that
shows a
form’s
Form
Header
5
The Application Object and
Properties
• Application object
 Represents the Access application that is currently
running
• MenuBar
 Identified the default menu that will be displayed when
the application opens
• Intrinsic constant
 Name used by Access to represent a particular integer
5
The Application Object and
Properties
Table 5-2 Some Application object properties
5
The Screen Object and
Properties
• Screen object
 Refers to the form, report, or control that is
currently active on the screen
• Some screen properties are read-only
 Macros and VBA statements can read the
current value but cannot change it
5
The Screen Object and
Properties
Table 5-3 Important Screen properties
5
Objects and Properties Related
to Forms, Reports, and Controls
Table 5-4
Commonly used
properties
associated with
format or
appearance
5
Objects and Properties Related
to Forms, Reports, and Controls
Table 5-5
Commonly
used
properties
associated
with
updating
data
5
Objects and Properties Related
to Forms, Reports, and Controls
Table 5-5
Commonly
used
properties
associated
with
updating
data
(continued)
5
Objects and Properties Related
to Forms, Reports, and Controls
• Caption property
 Displays text in the title bar of the form
• AllowAdditions
 A False setting prohibits the entry of new records
• Filter property
 When the FilterOn property equals True, the Filter
property restricts the rows that the form displays to
those rows that meet the criteria
5
Manipulating Record Sources
and Row Sources
• Row source
 Specifies a table or query that contains the values listed
in combo or list box
• Record source
 Identifies table or query that supplies data to the entire
form, page, or report
• Changing the value of a record source or row
source in a module or macro changes the data
displayed by the form, page, report, combo box, or
list box
5
Nuances of Forms, Reports,
and Controls Notation
• When the Modal property of a form is True, a
user cannot perform an operation on any other
form until the current, or modal, form is
closed
• When the PopUp property of a form property
is True, Access places the current form in front
of all other open forms
5
Nuances of Forms, Reports,
and Controls Notation
Figure 5-6
Creating a
hierarchical
sequence of
combo
boxes
5
Data Access Objects and
Their Properties
Table 5-6
Commonly
used DAO
objects
5
Data Access Objects and
Their Properties
Table 5-6
Commonly
used DAO
objects
(continued)
5
DBEngine, Workspace, and
the Database Objects
• Database object
 Represents an open database and is managed within the
context of a Workspace
• Workspace object
 Defines how an application interacts with data
• Database engine (DBEngine object)
 Manages workspaces
 Compromises the built-in procedures that retrieve and
update data in response to a user’s request
5
ActiveX Data Objects and
Properties
• ActiveX Data Objects model (ADO)
 Not hierarchical like the DAO model
Figure 5-7
ActiveX
Data
Objects
Database
Data
Model
(ADODB)
5
ActiveX Data Objects and
Properties
Figure 5-8
ActiveX
Data
Objects
Extensions
for DDL
and
Security
Data
Model
(ADOX)
5
ActiveX Data Objects and
Properties
Figure 5-9
ActiveX Data
Objects Jet
Replication
Objects Data
Model (JRO)
5
Libraries
• The libraries needed to support ADOX and
JRO are not automatically loaded by Access
• If using both DAO and ADO objects in an
Access project:
 Specify which library to use when declaring
objects
 DAO and ADO include several objects with the
same names
5
Connection Object
• Connection object
 Establishes a connection to the database and defines a
session in which a user interacts with the data
Table 5-7
Commonly used
ADODB
Connection
object properties
5
Connection Object
Table 5-7 Commonly used ADODB Connection object properties (continued)
5
Catalog and Table Objects
• Catalog object
 Represents a database
• Table object
 Contains the definitions that define the table
structures in the database
 Used to access properties of the table’s design
5
Table 5-8
Commonly
used Table
object
properties
Catalog and Table Objects
5
Catalog and Table Objects
• Some properties are optional
 Managed by the database engine only when
they are created in VBA code or set by the
system developer through Design or Datasheet
view of an object
 Description property
 Created by Microsoft Access (not the database
engine) through the Table property sheet
5
Field Objects and Properties
• Every Table object owns a Columns
collection
• As you append columns to the Columns
collection
 Specify the information needed for the Data
Type
5
Index Objects and Properties
• Indexes are fields used to find and sort records in a
table
• Keys are either primary keys or foreign keys
Table 5-9
Commonly
used ADOX
Index
properties
5
Command Object
• Command Object
 Can contain information needed to define a query
Table 5-10 Commonly used ADODB Command properties
5
Recordset Objects and
Properties
• Recordset objects
 Provide access to the actual data stored in and
retrieved from the database
• ADO recognizes four general types of
recordsets, which are determined by the
CursorType property setting
 Dynamic cursors
 Fully scrollable
5
Recordset Objects and
Properties
• Four general types of recordsets (cont.):
 KeySet cursors
 Fully scrollable, but do not offer the capability of
seeing changes made by other users
 Forward Only cursors
 Not scrollable
 Static cursors
 Fully scrollable, yet do not offer the option of seeing
changes made by anyone in a Static cursor
5
Recordset Objects and
Properties
Table 5-11 Allowed CursorType/LockType combinations in ADO
5
Recordset Objects and
Properties
Figure 5-10
Prospect
processing
module section
5
Recordset Objects and
Properties
• Procedure-level (or local variable)
 A variable used only in the current procedure
Table 5-12
Commonly
used
Recordset
properties
5
Recordset Objects and
Properties
• Bookmark property of a recordset
 Points to the current record
• EOF property
 Equals True when a previously executed Find
method does not find a record that matches a
specified criterion
 Equals True when the bookmark advances past
the last record in the recordset
5
Objects Defined by Other
Applications
• An Access application can use objects
managed by other applications
• To write code that uses objects from another
application:
 Microsoft Access References collection must
contain an object that identifies the other
application
5
Chapter Summary
• Access is an object-based database
environment
 Tables, queries, forms, reports, and even
database are objects
 Other objects that support data access activities
are recordsets and workspaces
5
Chapter Summary
• Access maintains hundreds of data items,
called properties, that describe the objects in
an Access database
• Objects are grouped into collections
 Collections are associated with the user
interface or with the database engine
5
Chapter Summary
• Generally, properties can be examined and
changed by opening the property sheet
associated with the object
• Most properties can be manipulated through
VBA
 Macros and VBA allow programmers to change
object properties while the program is running