Database Concepts
So
what is a database?
A database is an
organized collection of information. Common examples of a database would be a
telephone book, mailing list, recipe book, or a check book.
The
term database is a little different in Access. An Access database refers to a
set of data related to a specific purpose or topic, along with the tools needed
to utilize and manipulate that data, such as sorting, extracting, or
summarizing.
Database
Terminology
The first term to become familiar with is a Table.
An Access table is a list of related information presented in a column/row format.
A
table is broken down into additional components such as a row in a table. Each row
is referred to as a Record. So if you look up your information in a
telephone book you are reviewing your record.
Each
column in a table is a category of information referred to as a Field.
In a telephone book the column of phone numbers would be considered the Phone
Number field.
One
item of data, such as a single phone number, is called a Data Value.
Relational
Database Concepts
Prior to understanding the concept of a Relational Database you should
first understand the concept of a Flat File Database. A spreadsheet
would be considered a Flat File database.
Let's
use a mail-order Book company for example. If using a Flat File database one
item ordered would equal one record:
No
problem, right? But what happens if the customer orders several books?
In a flat file database the result would be multiple records and the majority
of the fields would contain duplicate data values:
This is not an efficiently designed database!
Now
let's look at the same data stored in a relational database. An Access relational
database is comprised of multiple tables each pertaining to a specific
topic:
Another
key piece to the relational database concept is each table contains a field, or
a combination of fields, in which the data value uniquely identifies the record
and Access will ensure that the data values remain unique to each record. This
field is referred to as the Primary Key. A Customer Id field
would be added to the Customers table and Book Id would be added
to Book Inventory table:
If each customer is assigned a unique value—their Customer Id—which is then
referenced when placing an order. The same would be true for Book Inventory.
Each Book is assigned a unique value that is referenced in the Orders table
when a book is purchased:
Thus the tables are related to each other by a common field.
A
table that contains the "parent" or "primary" information
can be linked to the appropriate row(s) in a table that contains
"child" or "related" information based on common key field
of the two tables.
Relationships
Each relationship will have a Primary (parent)
table and a Related (child) table as previously described.
An
easy way to determine the Primary table in the relationship is to note the
Primary key. Typically the Primary table is the table that holds the Primary
key field in the relationship.
In
the above image, the Customers table is the Primary table and the Orders table
would be the Related table.
How
each table is related to each other is another key concept in a relational
database.
There
are two main types of Relationships: One-to-One and One-to-Many.
There is a third relationship type called a Many-to-Many relationship,
but I'll cover that type of relationship in a future article.
One-to-One
Relationship
A one-to-one relationship exists when the primary
record will have only one related record. Another determining factor is both
fields used the relationship are Primary Key fields:
In the above image, you wouldn't want to assign the Customer ID to more than
one customer; therefore, the Primary Key field would be Cust ID. The same is
true for the Billing table.
Each
Customer should have only one Billing Address so the Cust ID field would be
designated as a Primary Key.
If
the related data value in both tables must be unique then there can only be one
matching record, thus a one-to-one relationship.
One-to-Many
Relationship
A one-to-many relationship is the most common type of
relationship. A one-to-many relationship exists when the primary record can
have many related records.
In this image, you can see that one customer can place many orders; therefore,
the Cust ID field in the Orders table can not be a Primary Key. This being the
case, many occurrences of the same Cust ID can be entered.
GUI development in
Visual Basic
It is often said that there are four main concepts in the area
of object-oriented programming:
·
Abstraction
·
Encapsulation
·
Inheritance
·
Polymorphism
Each of these concepts plays a significant role in VB 6
programming at one level or another. Encapsulation and abstraction are
"abstract" concepts providing motivation for object-oriented
programming. Inheritance and polymorphism are concepts that are directly
implemented in VB 6 programming.
Abstraction
Simply put, an abstraction is a view of an entity that includes only
those aspects that are relevant for a particular situation. For instance,
suppose that we want to create a software component that provides services for
keeping a company's employee information. For this purpose, we begin by making
a list of the items relevant to our entity (an employee of the company). Some
of these items are:
·
FullName
·
Address
·
EmployeeID
·
Salary
·
IncSalary
·
DecSalary
Note that we include not only properties of the entities in question, such as
FullName, but also actions that might be taken
with respect to these entities, such as IncSalary, to increase an employee's
salary. Actions are also referred to as methods, operations, or behaviors. We
will use the term methods, since this term is used by VB
6.
Of course, we would never think of including an IQ property,
since this would not be politically correct, not to mention discriminatory and
therefore possibly illegal. Nor would we include a property called HairCount,
which gives the number of hairs on the employee's right arm, because this
information is of absolutely no interest to us, even though it is part of every
person's being.
In short, we have abstracted the concept of an employee — we
have included only those properties and methods of employees that are relevant
to our needs. Once the abstraction is complete, we can proceed to encapsulate these properties and methods within a
software component.
Encapsulation
The idea of encapsulation is to
contain (i.e., encapsulate) the properties and methods of an abstraction, and
expose only those portions that are absolutely necessary. Each property and
method of an abstraction is called a member of the abstraction. The set of exposed members
of an abstraction is referred to collectively as the public interface (or just interface) of the abstraction (or of the software
component that encapsulates the abstraction).
Encapsulation serves three useful purposes:
·
It
permits the protection of these properties and methods from any outside
tampering.
·
It
allows the inclusion of validation code to help catch errors in the use of the
public interface. For instance, it permits us to prevent the client of the
employee software component from setting an employee's salary to a negative
number.
·
It
frees the user from having to know the details of how the properties and
methods are implemented.
Let us consider an example that involves the Visual Basic Integer data type, which is nicely encapsulated for us by
VB. As you undoubtedly know, an integer is stored in the memory of a PC as a
string of 0s and 1s called a binary
string. In Visual Basic, integers are interpreted in a form called two's-complement representation, which permits the representation of
both negative and non-negative values.
For simplicity, let us consider 8-bit binary numbers. An 8-bit
binary number has the form a7a6a5a4a3a2a1a0,
where each of the axs is a 0 or a 1. We can think of it as appearing
in memory.
An 8-bit binary number
In the two's-complement representation, the leftmost bit, a7
(called the most significant bit), is the sign bit. If the sign bit
is 1, the number is negative. If the sign bit is 0, the number is positive.
The formula for converting a two's-complement representation a7a6a5a4a3a2a1a0
of a number to a decimal representation is:
decimal rep. = -128a7 + 64a6 + 32a5 + 16a4 + 8a3 + 4a2 + 2a1 + a0
To take the negative of a number when it is represented in
two's-complement form, we must take the complement of each bit (that is, change
each 0 to a 1 and each 1 to a 0) and then add 1.
At this point you may be saying to yourself, "As a
programmer, I don't have to worry about these details. I just write code like:
x = -16
y = -x
and let the computer and the programming
language worry about which representation to use and how to perform the given
operations."
This is precisely the point behind encapsulation. The details of
how signed integers are interpreted by the computer (and the compiler), as well
as how their properties and operations are implemented, are encapsulated in the
integer data type itself and are thus hidden from us, the users of the data
type. Only those portions of the properties and operations that we need in
order to work with integers are exposed outside of the data type. These
portions form the public interface for the Integer data type.
Moreover, encapsulation protects us from making errors. For
instance, if we had to do our own negating by taking Boolean complements and
adding 1, we might forget to add 1! The encapsulated data type takes care of
this automatically.
Encapsulation has yet another important feature. Any code that
is written using the exposed interface remains valid even if the internal
workings of the Integer data type are changed for some reason, as long as the
interface is not changed. For instance, if we move the code to a computer that
stores integers in one's-complement representation, then the internal procedure for implementing the operation of
negation in the integer data type will have to be changed. However, from the
programmer's point of view, nothing has changed. The code:
x = -16
y = -x
is just as valid as before.
Interfaces
As VB programmers, we must
implement encapsulation through the use of software components. For instance,
we can create a software component to encapsulate the Employee abstraction
discussed earlier.
In VB 6, the methods of an interface are realized as functions. On the other
hand, a property, as we see later in this chapter, is realized as a private variable that stores the property's value
together with a pair of public functions — one
to set the variable and one to retrieve the variable. These functions are
sometimes referred to as accessor
methods of the property. It is the set of exposed functions (ordinary
methods and accessor methods) that constitute the interface
for an abstraction.
In general, a software component may encapsulate and expose more
than one abstraction — hence, more than one interface. For example, in a more
realistic setting, we might want a software component designed to model
employees to encapsulate an interface called IIdentification (the initial "I" is for
interface) that is used for identification purposes. This interface might have
properties such as name, Social Security number, driver's license number, age,
birthmarks, and so on. Moreover, the software component might also encapsulate
an interface called IEducation for describing the employee's educational
background. Such an interface might implement properties such as education
level, degrees, college attended, and so on.
The interface of each abstraction exposed by a software
component is also referred to as an interface of the software component. Thus,
the Employee component implements at least two interfaces: IIdentification and IEducation. Note, however, that the term
interface is often used to refer to the set of all
exposed properties and methods of a software component, in which case a
component has only one interface.
Referring to our original Employee abstraction, its interface
might consist of the functions. (Of course, this interface is vastly
oversimplified, but it is more than sufficient to illustrate the concepts.)
Using the term interface as a set of functions,
while quite common, poses a problem. Just listing the functions of the
interface by name (as done previously) does not provide enough information to
call those functions. Thus, a more useful definition of interface would be the
set of signatures of the public functions of a software component.
To clarify
this, let us discuss one of the most important distinctions in object- oriented
programming — the distinction between a function declaration and an implementation of that function.
By way of example, consider the following sorting function:
Function Sort(a( ) as Integer, iSize as Integer) as Boolean
For i = 1 to iSize
For j = i+1 to iSize
If a(j) < a(i) Then swap a(i), a(j)
Next j
Next I
Sort = True
End Function
The first line in this definition:
Function Sort(a( ) as Integer, iSize as Integer) as Boolean
is the function
declaration. It supplies information on the number and types of
parameters and the return type of the function. The body of the function:
For i = 1 to iSize
For j = i+1 to iSize
If a(j) < a(i) Then swap a(i), a(j)
Next j
Next i
Sort = True
represents the implementation
of the function. It describes how the function carries out its intended
purpose.
Note that it is possible to alter the implementation of the
function without changing the declaration. In fact, the current function
implementation sorts the array a using a simple selection-sort
algorithm, but we could replace that sorting method with any one of a number of
other methods (bubble sort, insertion sort, quick sort, and so on).
Now consider a client of the Sort
function. The client only needs to know the function declaration in order to
use the function. It need not know (and probably doesn't want to know) anything
about the implementation. Thus, it is the function declaration, and not the
implementation, that forms the interface for the function.
The signature of a function is the
function name and return type, as well as the names, order, and types of
its parameters. A function declaration is simply a clear way of describing the
function's signature. Note that Microsoft does not consider the return type of
a function to be part of the function's signature. By signature, they mean what
is generally termed the function's argument signature. The reasons for doing this become
clearer later in the chapter when we discuss overloading, although it would
have been better (as usual) if they were more careful with their terminology.
Under this more specific definition of interface, the interface
for our employee component might be as follows (in part):
Function GetFullName(lEmpID As Long) As String
Sub SetFullName(lEmpID As Long, sName As String)
. . .
Sub IncSalary(sngPercent As Single)
Sub DecSalary(sngPercent As Single)
Classes and Objects
Generally speaking, a class
is a software component that defines and implements one or more interfaces.
(Strictly speaking, a class need not implement all the members of an interface.
We discuss this later when we talk about abstract members.) In different terms,
a class combines data, functions, and types into a new type. Microsoft uses the
term type to include classes.
Class
Modules in VB 6
Under Visual StudioVB 6, a VB class
module is inserted into a project using the Add Class menu item on the Project
menu. This inserts a new module containing the code:
Public Class ClassName
End Class
Although Visual Studio stores each class in a separate file,
this isn't a requirement. It is the Class...End Class construct that marks the beginning
and end of a class definition. Thus, the code for more than
one class as well as one or more code modules (which are similarly delimited by
the Module...End Module
construct) can be contained in a single source code file.
The CPerson class defined in the next section is an example of a
VB class module.
Class
Members
In VB 6, class modules can contain the following
types of members:
Data
members
This
includes
member variables (also called fields) and
constants.
Event members
Events are
procedures that are called automatically by the Common Language Runtime in
response to some action that occurs, such as an object being created, a button
being clicked, a piece of data being changed, or an object going out of scope.
Function
members
This
refers
to both functions and subroutines. A function member is also called a method. A
class' constructor is a special type of method. We discuss constructors in
detail later in this chapter.
Property members
A property
member is implemented as a Private member variable together with a special type
of VB function that incorporates both accessor functions of the property.
Type
members
A
class member can
be another class, which is then referred to as a nested class.
The following CPerson class illustrates some of the types of
members:
Public Class CPerson
' -------------
' Data Members
' -------------
' Member variables
Private msName As String
Private miAge As Integer
' Member constant
Public Const MAXAGE As Short = 120
' Member event
Public Event Testing( )
' ----------------
' Function Members
' ----------------
' Method
Public Sub Test( )
RaiseEvent Testing( )
End Sub
Property Age( ) As Integer
Get
Age = miAge
End Get
Set(ByVal Value As Integer)
' Some validation
If Value < 0 Then
MsgBox("Age cannot be negative.") Else
miAge = Value
End If
End Set
End Property
' Property
Property Name( ) As String
' Accessors for the property
Get
Name = msName
End Get
Set(ByVal Value As String)
msName = Value
End Set
End Property
' Overloaded constructor
Overloads Sub New( )
End Sub
' Constructor that initializes name
Overloads Sub New(ByVal sNewName As String)
msName = sNewName
End Sub
Sub Dispose( )
' Code here to clean up
End Sub
End
Class
The Public
Interface of a VB 6 Class
We have seen
that, when speaking in general object-oriented terms, the exposed members of a
software component constitute the component's public interface (or just
interface). Now, in VB 6, each member of a class module has an access type,
which may be Public, Private, Friend, Protected, or Protected Friend. We discuss each of these in detail
later in this chapter. Suffice it to say, a VB 6 class module may accordingly
have Public, Private, Friend, Protected, and Protected Friend members.
Thus, we face some ambiguity in defining the concept of the
public interface of a VB 6 class. The spirit of the term might indicate that we
should consider any member that is exposed outside of the class itself as part
of the public interface of the class. This would include the Protected, Friend, and Protected
Friend members, as
well as the Public members. On the other hand, some might argue that the
members of the public interface must be exposed outside of the project in which
the class resides, in which case only the Public members would be included in the
interface. Fortunately, we need not make too much fuss over the issue of what
exactly constitutes a VB 6 class' public interface, as long as we remain aware
that the term may be used differently by different people.
Objects
A class is just a description of some properties and
methods and does not have a life of its own (with the exception of shared
members, which we discuss later). In general, to execute the methods and use
the properties of a class, we must create an instance of the class, officially
known as an object. Creating an instance of a class is referred to as instancing, or instantiating,
the class.
There are three ways to instantiate an object of a VB 6 class.
One method is to declare a variable of the class' type:
Dim APerson As CPerson
and then instantiate the object using the
New keyword as follows:
APerson = New CPerson( )
We can combine these two steps as follows:
Dim APerson As New CPerson( )
or:
Dim APerson As CPerson = New CPerson( )
The first syntax is considered shorthand for the second.
Properties
Properties are members that can be implemented in two different ways. In
its simplest implementation, a property is just a public variable, as in:
Public Class CPerson
Public Age As Integer
End Class
The problem with this implementation of the Age property is that
it violates the principle of encapsulation; anyone who has access to a CPerson
object can set its Age property to any Integer value, even negative integers,
which are not valid ages. In short, there is no opportunity for data
validation. (Moreover, this implementation of a property does not permit its
inclusion in the public interface of the class, as we have defined that term.)
The "proper" object-oriented way to implement a
property is to use a Private data member along with a special pair of function
members. The Private data member holds the property value; the pair of function
members, called accessors,
are used to get and set the property value. This
promotes data encapsulation, since we can restrict access to the property via
code in the accessor functions, which can contain code to validate the data.
The following code implements the Age property:
Private miAge As Integer
Property Age( ) As Integer
Get
Age = miAge
End Get
Set(ByVal Value As Integer)
' Some validation
If Value < 0 Then
MsgBox("Age cannot be negative.") Else
miAge = Value
End If
End Set
End Property
As you can see from the previous code, VB has a special syntax
for defining the property accessors. As soon as we finish typing the line:
Property Age( ) As Integer
the VB IDE automatically creates the
following template:
Property Age( ) As Integer
Get
End Get
Set(ByVal Value As Integer)
End Set
End Property
Note the Value parameter that provides access to
the incoming value. Thus, if we write:
Dim cp As New CPerson( )
cp.Age = 20
then VB passes the value 20 into the
Property procedure in the Value argument.
Instance and Shared Members
The members of
a class fall into two categories:
Instance
members
Members that can only be accessed through an instance of
the class, that is, through an object of the class.
To put it another way, instance members "belong" to an individual
object rather than to the class as a whole.
Shared (static)
members
Members that can be accessed without
creating an instance of the class. These members are shared among all instances of the class. More
correctly, they are independent of any particular object of the class. To put
it another way, shared members "belong" to the class as a whole,
rather than to its individual objects or instances.
Instance members are accessed by qualifying the member name with
the object's name. Here is an example:
Dim APerson As New CPerson( )
APerson.Age = 50
To access a shared member, we simply qualify the member with the
class name. For instance, the String class in the System namespace of the VB 6
Framework Class Library has a shared method called Compare that compares two
strings. Its syntax (in one form) is:
Public Shared Function Compare(String, String) As Integer
This function returns 0 if the strings are equal, -1 if the
first string is less than the second, and 1 if the first string is greater than
the second. Since the method is shared, we can write:
Dim s As String = "steve"
Dim t As String = "donna"
MsgBox(String.Compare(s, t)) ' Displays 1
Note the way the Compare method is qualified with the name of
the String class.
Shared members are useful for keeping track of data that is
independent of any particular instance of the class. For instance, suppose we
want to keep track of the number of CPerson objects in existence at any given
time. Then we write code such as the following:
' Declare a Private shared variable to hold the instance count
Private Shared miInstanceCount As Integer
' Increment the count in the constructor
' (If there are additional constructors,
' this code must be added to all of them.)
Sub new( )
miInstanceCount += 1
End Sub
' Supply a function to retrieve the instance count
Shared Function GetInstanceCount( ) As Integer
Return miInstanceCount
End Function
' Decrement the count in the destructor
Overrides Protected Sub Finalize( )
miInstanceCount -= 1
MyBase.Finalize
End Sub
Now, code such as the following accesses the shared variable:
Dim steve As New CPerson( )
MsgBox(CPerson.GetInstanceCount) ' Displays 1
Dim donna As New CPerson( )
MsgBox(CPerson.GetInstanceCount) ' Displays 2
Class
Constructors
When an object
of a particular class is
created, the compiler calls a special function called the class' constructor or instance
constructor. Constructors can be used to initialize an object when
necessary. (Constructors take the place of the Class_ Initialize event in
earlier versions of VB.)
We can define constructors in a class module. However, if we
choose not to define a constructor, VB uses a default constructor. For
instance, the line:
Dim APerson As CPerson = New CPerson( )
invokes the default constructor of our
CPerson class simply because we have not defined a custom constructor.
To define a custom constructor, we just define a subroutine
named New within the class module. For
instance, suppose we want to set the Name property to a specified value when a
CPerson object is first created. Then we can add the following code to the
CPerson class:
' Custom constructor
Sub New(ByVal sName As String)
Me.Name = sName
End Sub
Now we can create a CPerson object and set its name as follows:
Dim APerson As CPerson = New CPerson("fred")
or:
Dim APerson As New CPerson("fred")
Note that because VB 6 supports function overloading (discussed
later in this chapter), we can define multiple constructors in a single class,
provided each constructor has a unique argument signature. We can then invoke
any of the custom constructors simply by supplying the correct number and type
of arguments for that constructor.
Note also that once we define one or more custom constructors,
we can no longer invoke the default (that is, parameterless)
constructor with a statement such as:
Dim APerson As New CPerson( )
Instead, to call a parameterless constructor, we must
specifically add the constructor to the class module:
' Default constructor
Sub New( )
...
End Sub
Finalize,
Dispose, and Garbage Collection
In VB 6, a
programmer can implement the Class_Terminate event to
perform any clean up procedures before an object is destroyed. For instance, if
an object held a reference to an open file, it might be important to close the
file before destroying the object itself.
When the garbage collector determines that an object is no
longer needed (which it does, for instance, when the running program no longer
holds a reference to the object), it automatically runs a special destructor method called Finalize. However, it is important to understand that,
unlike with the Class_Terminate event, we have no way to determine exactly when
the garbage collector will call the Finalize method. We can only be sure that
it will be called at some time after the last reference to the object is released.
Any delay is due to the fact that the VB 6 Framework uses a system called reference-tracing garbage
collection, which periodically releases unused resources.
Finalize is a Protected method. That is, it can be called from a
class and its derived classes, but it is not callable from outside the class,
including by clients of the class. (In fact, since the Finalize destructor is
automatically called by the garbage collector, a class should never call its
own Finalize method directly.) If a class' Finalize method is present, then it
should explicitly call its base class' Finalize method as well. Hence, the
general syntax and format of the Finalize method is:
Overrides Protected Sub Finalize( )
' Cleanup code goes here
MyBase.Finalize
End Sub
The benefits of garbage collection are that it is automatic and
it ensures that unused resources are always released without any specific
interaction on the part of the programmer. However, it has the disadvantages
that garbage collection cannot be initiated directly by application code and
some resources may remain in use longer than necessary. Thus, in simple terms,
we cannot destroy objects on cue.
We should note that not all resources are managed by the Common
Language Runtime. These resources, such as Windows handles and database
connections, are thus not subject to garbage collection without specifically
including code to release the resources within the Finalize method. But, as we
have seen, this approach does not allow us or clients of our class to release
resources on demand. For this purpose, the Framework Class Library defines a
second destructor called Dispose. Its general syntax
and usage is:
Class classname
Implements IDisposable
Public Sub Dispose( ) Implements IDisposable.Dispose
' cleanup code goes here
' call child objects' Dispose methods, if necessary, here
End Sub
' Other class code
End Class
Note that classes that support this callable destructor must
implement the IDisposable interface — hence the Implements statement just shown. IDisposable has just one member, the Dispose
method.
It is important to note that it is necessary to inform any
clients of the class that they must call this method specifically in order to
release resources. (The technical term for this is the manual approach!)
Inheritance
Perhaps the best way to describe inheritance as it is
used in VB 6 is to begin with an example.
The classes in a given application often have relationships to
one another. Consider, for instance, our Employee information application. The
Employee objects in the class CEmployee represent the general aspects common to
all employees — name, address, salary, and so on.
Of course, the executives of the company will have different
prerequisites than, say, the secretaries. So it is reasonable to define
additional classes named CExecutive and CSecretary, each with properties and
methods of its own. On the other hand, an executive is also an employee, and
there is no reason to define different Name properties in the two cases. This
would be inefficient and wasteful.
This situation is precisely what inheritance is designed for.
First, we define the CEmployee class, which implements a Salary property and an
IncSalary method:
' Employee class
Public Class CEmployee
' Salary property is read/write
Private mdecSalary As Decimal
Property Salary( ) As Decimal
Get
Salary = mdecSalary
End Get
Set
mdecSalary = Value
End Set
End Property
Public Overridable Sub IncSalary(ByVal sngPercent As Single)
mdecSalary = mdecSalary * (1 + CDec(sngPercent))
End Sub
End Class
Next, we define the CExecutive class:
' Executive Class
Public Class CExecutive
Inherits CEmployee
' Calculate salary increase based on 5% car allowance as well
Overrides Sub IncSalary(ByVal sngPercent As Single)
Me.Salary = Me.Salary * CDec(1.05 + sngPercent)
End Sub
End Class
There are two things to note here. First, the line:
Inherits CEmployee
indicates that the CExecutive class inherits the members of the CEmployee class. Put
another way, an object of type CExecutive is also an object of type CEmployee.
Thus, if we define an object of type CExecutive:
Dim ceo As New CExecutive
then we can invoke the Salary property,
as in:
ceo.Salary = 1000000
Second, the keyword Overrides in the IncSalary method means that
the implementation of IncSalary in CExecutive is called instead of the
implementation in CEmployee. Thus, the code:
ceo.IncSalary
raises the salary of the CExecutive object ceo based on a car allowance. Note also
the presence of the Overridable keyword in the definition of
IncSalary in the CEmployee class, which specifies that the class inheriting
from a base class is allowed to override
the method of the base class.
Next, we define the CSecretary class, which also inherits from
CEmployee but implements a different salary increase for secretary objects:
' Secretary Class
Public Class CSecretary
Inherits CEmployee
' Secretaries get a 2% overtime allowance
Overrides Sub IncSalary(ByVal sngPercent As Single)
Me.Salary = Me.Salary * CDec(1.02 + sngPercent)
End Sub
End Class
We can now write code to exercise these classes:
' Define new objects
Dim ThePresident As New CExecutive( )
Dim MySecretary As New CSecretary( )
' Set the salaries
ThePresident.Salary = 1000000
MySecretary.Salary = 30000
' Set Employee to President and inc salary
Debug.Writeline("Pres before: " & CStr(ThePresident.Salary))ThePresident.IncSalary(0.4)
Debug.WriteLine("Pres after: " & CStr(ThePresident.Salary))
Debug.Writeline("Sec before: " & CStr(MySecretary.Salary))MySecretary.IncSalary(0.3)
Debug.Writeline("Sec after: " & CStr(MySecretary.Salary))
The output in this case is:
Pres before: 1000000
Pres after: 1450000
Sec before: 30000
Sec after: 39600
The notion of inheritance is quite simple, as put forth in
Microsoft's documentation:
If Class B inherits from Class A, then any object of Class B is
also an object of Class A and so includes the public properties and methods
(that is, the public interface) of Class A. In this case, Class A is called the
base class and Class B is called the derived class. On the
other hand, in general, the derived class can override
the implementation of a member of the base class for its own use.
We have seen in the previous example that inheritance is
implemented using the Inherits keyword.
Permission
to Inherit
There are two keywords used in the base
class definition that affect the ability to inherit from a base class:
NotInheritable
When
this is used to define a class, as in:
Public NotInheritable Class InterfaceExample
the class cannot be used as a base
class.
MustInherit
When this is used to define a class,
as in:
Public MustInherit Class InterfaceExample
objects of this class cannot be created
directly. Objects of a derived class can be created, however. In other words, MustInherit classes can be used as base classes
and only as base classes.
Overriding
There are
several keywords that control whether a derived class
can override an implementation in the base class. These keywords are used in
the declaration of the member in question, rather than in the class definition:
Overridable
Allows
but does not require a member to be overridden. Note that the default for a Public
member is NotOverridable.
Here is an example:
Public Overridable Sub IncSalary( )
NotOverridable
Prohibits
overriding of the member. This is the default for Public members of a class.
MustOverride
Must be overridden.
When this keyword is used, the member definition is restricted to just the
declaration line, with no implementation and no End
Sub
or End
Function
line. For example:
Public MustOverride Sub IncSalary( )
Note
also that when a class module contains a MustOverride member, then the
class itself must be declared as MustInherit.
Overrides
Unlike the other modifiers, this
modifier belongs in the derived class and indicates that the modified member is
overriding a base class member. For example:
Overrides Sub IncSalary( )
Rules of Inheritance
In many object-oriented languages, such
as C++, a class can inherit directly from more than one base class. This is
referred to as multiple inheritance. VB 6
does not support multiple inheritance, and so a class
can inherit directly from at most one other class. Thus, code such as the
following is not permitted:
' Executive Class
Public Class CExecutive 'INVALID
Inherits CEmployee
Inherits CWorker
. . .
End Class
On the other hand, Class C can inherit from Class B, which, in
turn, can inherit from Class A, thus forming an inheritance hierarchy. Note
also that a class can implement multiple interfaces through the Interface keyword. We discuss this issue later
in this chapter.
MyBase,
MyClass, and Me
The keyword MyBase provides a reference to the base
class from within a derived class. If you want to call
a member of the base class from within a derived class, you can use the syntax:
MyBase.MemberName
where MemberName is the name of the member. This will
resolve any ambiguity if the derived class also has a member of the same name.
The MyBase keyword can be used to call the constructor of the base
class in order to instantiate a member of that class, as in:
MyBase.New(...)
Note that MyBase cannot be used to call Private class members.
Visual Basic looks for the most immediate version in parent
classes of the procedure in question. Thus, if Class C derives from Class B,
which derives from Class A, a call in Class C to:
MyBase.AProc
first looks in Class B for a matching
procedure named AProc. If none is found, then
VB looks in Class A for a matching procedure. (By matching,
we mean a method with the same argument signature.)
The keyword MyClass provides a reference to the class in
which the keyword is used. It is similar to the Me keyword, except when used to call a
method. To illustrate the difference, consider a class named Class1 and a
derived class named Class1Derived. Note that each class has an IncSalary
method:
Public Class Class1
Public Overridable Function IncSalary(ByVal sSalary As Single) _
As Single
IncSalary = sSalary * CSng(1.1)
End Function
Public Sub ShowIncSalary(ByVal sSalary As Single)
MsgBox(Me.IncSalary(sSalary))
MsgBox(MyClass.IncSalary(sSalary))
End Sub
End Class
Public Class Class1Derived
Inherits Class1
Public Overrides Function IncSalary(ByVal sSalary As Single) _
As Single
IncSalary = sSalary * CSng(1.2)
End Function
End Class
Now consider the following code, placed in a form module:
Dim c1 As New Class1( )
Dim c2 As New Class1Derived( )
Dim c1var As Class1
c1var = c1
c1var.IncSalary(10000) ' Shows 11000, 11000
c1var = c2
c1var.IncSalary(10000) ' Shows 12000, 11000
The first call to IncSalary is made using a variable of type
Class1 that refers to an object of type Class1. In this case, both of the
following calls:
Me.IncSalary
MyClass.IncSalary
return the same value, because they both
call IncSalary in the base class Class1.
However, in the second case, the variable of type Class1 holds a
reference to an object of the derived class, Class1Derived. In this case, Me refers to an object of type Class1Derived, whereas MyClass still refers to the base class
Class1 wherein the keyword MyClass appears. Thus,
Me.IncSalary
returns 12000 whereas the following:
MyClass.IncSalary
returns 10000.
Shadowing
VB 6 has a feature referred to as shadowing that is similar to overriding, but with
some very important differences. Shadowing can apply to element types
associated with any of the following statements:
Class Statement
Constant Statement
Declare Statement
Delegate Statement
Dim Statement
Enum Statement
Event Statement
Function Statement
Interface Statement
Property Statement
Structure Statement
Sub Statement
The best way to illustrate shadowing and the differences between
shadowing and overriding is
with an example. Consider two classes, Class1 and Class2, where Class2 derives
from Class1:
Public Class Class1
Public x As Integer = 1
Public Overridable Sub TestOverride( )
MsgBox("Class1 method to override") End Sub
Public Sub TestShadow( )
MsgBox("Class1 method to shadow") End Sub
End Class
Public Class Class2
' Derived class
Inherits Class1
Public Shadows x As Integer = 2
Public Overrides Sub TestOverride( )
MsgBox("Class2 method that overrides") End Sub
Public Shadows Sub TestShadow( )
MsgBox("Class2 method that shadows") End Sub
End Class
Class1 has two methods, TestOverride and TestShadow. Note that
TestOverride is declared with the Overridable keyword.
Class2 also defines two methods with the names TestOverride and TestShadow.
Note that TestOverride is declared with the Overrides keyword,
and TestShadow is declared with the Shadows keyword.
Finally, note the presence of a public instance field, x, in each class.
Now, consider the following test code:
Dim c2 As Class2 = New Class2( )
c2.TestOverride( )
c2.TestShadow( )
MsgBox("x=" & c2.x)
Because the object reference c2 is to an object of Class2, the
calls to the TestOverride and TestShadow methods, as well as the public
variable x, all refer to code in Class2, so the output messages are as
expected:
Class2 method that overrides
Class2 method that shadows
x = 2
Now consider the code:
Dim c1 As Class1 = New Class2( )
c1.TestOverride( )
c1.TestShadow( )
MsgBox("x=" & c1.x)
Here, we have a variable of type Class1 that refers to an object
of Class2. The output in this case is:
Class2 method that overrides
Class1 method that shadows
x = 1
To explain this, note that overriding works as follows: the
method that is called is the version that is implemented not in the type
(class) of the variable, but in the type (class) of the object to which that
variable refers. This is a key feature of overriding and is generally referred
to as a form of polymorphism. (The variable c1 takes
on many forms, based on the type of object to which it refers, rather than its
own type.)
On the other hand, shadowing is different from overriding: the
process is not polymorphic, and so it is the type of the variable itself and
not the referenced object that determines the implementation that is used.
Since the variable has type Class1, the VB 6 compiler ignores the "extra
goodies" that exists because c1 happens to point to a derived class object
and looks only at the Class1 portion of the object, so to speak. There is no
polymorphism here.
Note that member variables, such as x, can only be shadowed; they
cannot be overridden.
One other difference between shadowing and overriding is that
any element type in the preceding list can shadow any other element type. For
instance, a method in the derived class can shadow a variable of the same name
in the base class.
Unfortunately, the Microsoft documentation makes this point at
the expense of the real issue, that of polymorphism. After all, it would seem
to be bad programming practice to shadow elements of different types. But
shadowing methods may make some sense.
Shadowing occurs in another context that is referred to as shadowing by scope. For example, if a module
contains a Public variable declaration and one of the procedures within the
module contains a variable declaration of the same name but perhaps a different
data type, then within the procedure, the local variable will shadow the
module-level variable.
Interfaces,
Abstract Members, and Classes
We have alluded to the fact that a class may
implement all, some, or none of the members of the interfaces that it defines.
Any interface member that does not have an implementation is referred to as an abstract member. The purpose of an abstract member is
to provide a member signature (a template, if
you will) that can be implemented by one or more derived classes, generally in different ways.
Let us clarify this with an example. Recall from our discussion
of inheritance that the CEmployee class defines and implements an IncSalary
method that increments the salary of an employee. Recall also that the CExecutive
and CSecretary derived classes override the implementation of the IncSalary
method in the base class CEmployee.
Suppose that, in a more complete employee model, there is a
derived class for every type of employee. Moreover, each of these derived
classes overrides the implementation of the IncSalary method in the base class
CEmployee. In this case, the implementation of IncSalary in the base class will
never need to be called! So why bother to give the member an implementation
that will never be used?
Instead, we can simply provide an empty IncSalary method, as
shown here:
' Employee class
Public Class CEmployee
. . .
Public Overridable Sub IncSalary(ByVal sngPercent As Single)
End Sub
End Class
Alternatively, if we want to require
that all derived classes implement the IncSalary method, we can use the MustOverride keyword, as shown here:
' Employee class
Public MustInherit Class CEmployee
. . .
Public MustOverride Sub IncSalary(ByVal sngPercent As Single)
End Class
As mentioned earlier, when using MustOverride, there is no End Sub statement associated with the
method. Note also that when using the MustOverride keyword, Microsoft requires that the
class be declared with the MustInherit keyword. This specifies that we
cannot create objects of type CEmployee.
In each of the previous cases, the IncSalary member of the base
class CEmployee is an abstract member.
Any class that contains at least one abstract member is termed
an abstract class.
(Thus, the CEmployee class as defined earlier is an abstract class.) This
terminology comes from the fact that it is not possible to create an object
from an abstract class because at least one of the object's methods would not
have an implementation.
There are also situations where we might want to define a class
in which all members are abstract. In other words, this is a class that only defines an interface. We might refer to such a class
as a pure abstract class, although this
terminology is not standard.
For example, imagine a Shape class called CShape that is
designed to model the general properties and actions of geometric shapes
(ellipses, rectangles, trapezoids, etc.). All shapes need a Draw method, but
the implementation of the method varies depending on the type of shape —
circles are drawn quite differently than rectangles, for example. Similarly, we
want to include methods called Rotate, Translate, and Reflect, but, as with the
Draw method, each of these methods requires a different implementation based on
the type of shape.
Thus, we can define the CShape class in either of the following
ways:
Public Class Class2
Public Overridable Sub Draw( )
End Sub
Public Overridable Sub Rotate(ByVal sngDegrees As Single)
End Sub
Public Overridable Sub Translate(ByVal x As Integer, _
ByVal y As Integer)
End Sub
Public Overridable Sub Reflect(ByVal iSlope As Integer, _
ByVal iIntercept As Integer)
End Sub
End Class
or:
Public MustInherit Class CShape
Public MustOverride Sub Draw( )
Public MustOverride Sub Rotate(ByVal sngDegrees As Single)
Public MustOverride Sub Translate(ByVal x As Integer, _
ByVal y As Integer)
Public MustOverride Sub Reflect(ByVal iSlope As Integer, _
ByVal iIntercept As Integer)
End Class
Now we can define derived classes such as CRectangle, CEllipse,
and CPolygon. Each of these derived classes will (or must, in the latter case) implements
the members of the base class CShape. (We won't go into the details of such an
implementation here, since it is not relevant to our discussion.)
Interfaces
Revisited
We have seen that
interfaces can be defined in class modules. VB 6 also supports an additional
method of defining an interface, using the Interface keyword. The following example
defines the IShape interface:
Public Interface IShape
Sub Draw( )
Sub Rotate(ByVal sngDegrees As Single)
Sub Translate(ByVal x As Integer, ByVal y As Integer)
Sub Reflect(ByVal iSlope As Integer, _
ByVal iIntercept As Integer)
End Interface
Note that we cannot implement
any of the members of an interface defined using the Interface keyword — that is, not within the module
in which the interface is defined. However, we can implement the interface
using an ordinary class module. Note the use of the Implements statement (which was also available
in VB 6, but could be applied only to external interfaces):
Public Class CRectangle
' Implement the interface IShape
Implements IShape
Public Overridable Sub Draw( ) Implements IShape.Draw
' code to implement Draw for rectangles
End Sub
Public Overridable Sub Spin( ) Implements IShape.Rotate
' code to implement Rotate for rectangles
End Sub
End Class
Note also the use of the Implements keyword in each function that
implements an interface member. This keyword allows us to give the implementing
function any name — it does not need to match the name of the method (see the
Spin method earlier in this section, which implements the IShape interface's Rotate
method). However, it is probably less confusing (and better programming
practice) to use the same name.
The main advantage of using the Implements keyword approach to defining an
interface is that a single class can implement multiple interfaces, whereas VB
6 does not permit a single class to inherit directly from multiple base
classes. On the other hand, the main disadvantage of the Interface keyword approach is that no
implementation is possible in the module that defines the interface. Thus, all interface members must be implemented in every class that implements the interface. This can
mean code repetition if an interface member has the same implementation in more
than one implementing class.
Polymorphism and
Overloading
Fortunately, we don't need to go into the details of
polymorphism and overloading, which is just as well, because they tend to be
both confusing and ambiguous. For instance, some computer scientists say that
overloading is a form of polymorphism, whereas others say it is not. We will
discuss only those issues that are directly relevant to the VB 6 Framework.
Overloading
Overloading refers to an item being used in more than one way. Operator
names are often overloaded. For instance, the plus sign (+) refers to addition
of integers, addition of singles, addition of doubles, and concatenation of
strings. Thus, the plus symbol (+) is overloaded. It's a good thing, too;
otherwise, we would need separate symbols for adding integers, singles, and
doubles.
Function names can also be overloaded. For instance, the
absolute value function, Abs, can take an
integer parameter, a single parameter, or a double parameter. Because the name Abs represents several different functions, it is
overloaded. In fact, if you look at the documentation for the Abs member of the
Math class (in the System namespace of the Framework Class Library), you will
find the following declarations, showing the different functions using the Abs
name:
Overloads Public Shared Function Abs(Decimal) As Decimal
Overloads Public Shared Function Abs(Double) As Double
Overloads Public Shared Function Abs(Integer) As Short
Overloads Public Shared Function Abs(Integer) As Integer
Overloads Public Shared Function Abs(Long) As Long
Overloads Public Shared Function Abs(SByte) As SByte
Overloads Public Shared Function Abs(Single) As Single
Note the use of the Overloads keyword, which tells VB that this
function is overloaded.
Specifically, a function name is overloaded when two defined
functions use the same name but have different argument signatures. For instance, consider a
function that retrieves a current account balance. The account could be
identified either by the person's name or by the account number. Thus, we might
define two functions, each called GetBalance:
Overloads Function GetBalance(sCustName As String) As Decimal
Overloads Function GetBalance(sAccountNumber As Long) As Decimal
Note also that VB 6 permits function overloading only because
the argument signatures of the two functions are different, so that no
ambiguity can arise. The function calls:
GetBalance("John Smith")GetBalance(123456)
are resolved by the compiler without
difficulty, based on the data type of the argument. This type of overloading is
often referred to as overloading the function GetBalance.
On the other hand, there are two different functions here, so it seems more
appropriate to say that the function name is
being overloaded. Overloading is very common and not exclusive to
object-oriented programming.
Polymorphism
The term polymorphism
means having or passing through many different forms. In the VB 6 Framework,
polymorphism is tied directly to inheritance. Again, let us consider our
Employee example. The function IncSalary is
defined in three classes: the base class CEmployee and the derived classes
CExecutive and CSecretary. Thus, the IncSalary
function takes on three forms. This is polymorphism, VB 6 style.
In case you are interested, many computer scientists would not
consider this to be polymorphism. They would argue that the function IncSalary takes on only one form. It is the
implementation that differs, not the function. They would refer to the
situation described here for IncSalary as function
overloading. The main point here is that there is a lot of confusion as to how
Microsoft and others use the terms overloading and polymorphism, so you should
be on guard when reading documentation.
Accessibility in
Class Modules
The notion of accessibility (or scope) in class
modules is more involved than it is in standard modules. As far as local
variables (block-level and procedure-level) are concerned, there is no
difference — we have block scope and procedure-level scope.
However, members of a class
module can be assigned one of the following access modifiers:
·
Public
·
Private
·
Friend
·
Protected
·
Protected Friend
(For standard modules, only Public, Private, and Friend are allowed.)
Actually, we can dispense with the Protected Friend modifier in one statement: Protected Friend is equivalent to Protected or Friend. Put another way, if Protected sets a specific range of
accessibility (or inheritance — see below) and Friend sets a different range, then Protected Friend sets accessibility to the union of those ranges — if a member falls into either range, it passes the accessibility (or
inheritance) criterion.
Note that class modules themselves can be declared with any one
of the three access modifiers: Public, Private, or Friend (Protected is not allowed). When a class module
declaration specifies one of these access modifiers, this simply restricts all
of its members to that level of access, unless a member's access is further
restricted by the access modifier on the member declaration itself. For instance,
if the class has Friend access, no member can have Public access. (Put another way, the Public access is overridden by the Friend class access.)
On the other hand, all four access modifiers apply to members of
the class module — that is, to variable, constant, enum, and procedure
declarations within the class module.
To avoid confusion in discussing the access modifiers, it helps
to separate the issue of accessibility of members from that of inheritance of
members.
Member
Inheritance
Let us first
address member inheritance. Suppose that a class named Class1 has a derived
class named Class1Derived, as shown in the following:
Public Class Class1
Public pub As Integer = 1
Private priv As Integer = 1
Protected p As Integer = 1
Friend f As Integer = 1
Protected Friend pf As Integer = 1
End Class
Public Class Class1Derived
Inherits Class1
Public Sub test( )
' Not allowed - private members are not inherited
Me.priv = 4
' Allowed only in derived classes in the same project as base class
Me.f = 4
' Allowed in all derived classes
Me.pub = 4
Me.p = 4
Me.fp = 4
End Sub
End Class
Note that the Me.
syntax is optional, and we could write, for instance, simply:
pub = 4
p = 4
f = 4
fp = 4
The fact that the code:
Me.p = 4
is valid in Class1Derived means that
this class has inherited the member p. In other
words, an object of class Class1Derived has a member variable named p. The fact
that:
Me.f
fails to work in Class1Derived if
Class1Derived is in a different project than Class1 means that such classes do
not inherit the member f.
Now, the rules of inheritance are:
·
Private
members are never inherited.
·
Public
members are inherited by all derived classes.
·
Protected
members are inherited by all derived classes (and therefore so are Protected
Friend members).
·
Friend
members are inherited by all derived classes in the same project as the base
class only.
Member
Accessibility
Now we come to member
accessibility. Unfortunately, the term accessibility is used quite loosely in
most documentation, but to make absolutely clear sense of the issue, we must be
specific. Many writers simply refer to a member's accessibility, but this is
ambiguous. To illustrate, consider the code:
Public Class Class1
Public x As Integer = 1
End Class
Public Class Class2
Inherits Class1
End Class
Now, it makes sense to ask about the accessibility of the Public
member x of Class1 or the (inherited) Public member x of Class2. It does not
make sense to ask about the accessibility of the member x alone, without
mention of the class involved. Indeed, we say that the Public member x of
Class1 is accessible from a class Class3 if the following is legal:
Public Class Class3
Public Sub Test( )
Dim c1 As new Class1( )
c1.x = 5
End Sub
End Class
On the other hand, the Public member x of class Class2 is
accessible from Class3 if we can write:
Public Class Class3
Public Sub Test( )
Dim c2 As new Class2( )
c2.x = 5
End Sub
End Class
With this in mind, we can describe the accessibility
rules clearly:
Private
If
ClassA is a class with a Private member m, we cannot access the member m of
ClassA from any other class.
Public
If ClassA is a class with a Public
member m, we can access the member m of ClassA from any other class.
Friend
If
ClassA is a class with a Friend member m, we can access the member m of ClassA
from any other class that is in the same project as ClassA.
Protected
Let m be a Protected
member of ClassA. Then from any subclass ClassB of ClassA, we can access the
member m of ClassB or the member m of any subclass of ClassB. Another way to
phrase this is as follows. Let m be declared as Protected in a class ClassA.
Let Class B be a subclass of ClassA. Then the member m of ClassB is accessible
in each class between ClassB and ClassA in the inheritance hierarchy.
Clearly, the definition of Protected needs clarification. To do so,
consider a chain of derived classes (that is, ClassN+1 is derived from ClassN):
Class1
Class2
.
.
.
ClassA
' This is the first appearance of the protected method MyMethod
' Thus, all classes below inherit MyMethod
Protected Sub MyMethod( )
.
.
.
ClassB
' ClassB can call MyMethod because it has been inherited
' This is accessibility of MyMethod for ClassB
MyMethod( )
' Note that this is equivalent to
Me.MyMethod( )
' Can access MyMethod for any Class at or below this class
' For example, the following are legal:
Dim b As New ClassB
b.MyMethod( )
Dim c As New ClassC
c.MyMethod( )
' But the following is not legal
Dim a As New ClassA
a.MyMethod( )
.
.
.
ClassC
' Can access MyMethod for any Class at or below this class
' For example, the following are legal:
Me.MyMethod( )
Dim c As New ClassC
c.MyMethod( )
Dim d As New ClassD
d.MyMethod( )
' But the following is not legal
Dim b As New ClassB
b.MyMethod( )
.
.
.
ClassD
.
.
.
ClassN
As you can see, the rules for Protected mode access are a bit involved. Actually, Protected mode should be used with some care. For instance,
declaring a member variable Protected violates one of the principal rules
of good object-oriented programming, encapsulation,
as does declaring the member Public. Thus, it should be done only if you are
certain that derived classes will be well behaved (or are willing to accept
the risk). The same applies to
Protected
methods.
ADO and Data Binding
Access 2000 continues Microsoft's strategy of emphasizing
"Universal Data Access" for Windows database programming. Microsoft
wants all Office users, not just Access developers, to abandon Data Access
Objects (DAO), ODBCDirect, and the Open
Database Connectivity (ODBC) Application Programming Interface (API) in favor
of a collection of Component Object Model (COM) interfaces called OLE DB and ActiveX Data Objects (ADO). To encourage Access users and developers to adopt OLE DB and ADO, all traditional
Microsoft database technologies (referred to by Microsoft as downlevel, a synonym for
"obsolete") are destined for maintenance mode. Maintenance mode is a
technological purgatory in which Microsoft fixes only the worst bugs and upgrades occur infrequently, if ever. In
1999, OLE DB, ADO, and
for Jet programmers, ActiveX Data Object Extensions (ADOX), became Microsoft's
mainstream data access technologies.
Microsoft's primary goals for Universal Data Access are
to
·
Provide
the capability to accommodate less common data types unsuited to SQL queries,
such as directory services (specifically Active Directory), spreadsheets, email
messages, and file systems
·
Minimize
the size and memory consumption of the
dynamic link libraries (DLLs) required to support data access on Internet and intranet clients (PCs and handheld "Internet appliances")
·
Reduce
development and support costs for the
multiplicity of Windows-based data access architectures in common use today
·
Extend
the influence of COM and COM+ in
competition with other object models, primarily Common Object Request Broker
Architecture (CORBA) and its
derivatives
This lesson introduces the fundamentals of Universal Data Access
and Microsoft Data Access Components
(MDAC). MDAC makes connecting
to databases with OLE DB practical
for Access users and developers. MDAC
includes ADO and
ADOX for conventional data handling, plus ADOMD for multidimensional expressions
(MDX) to create and manipulate data
cubes.
Note
Microsoft SQL Server Analysis Services (formerly OLAP Services)
generates data cubes from online sources, such as transactional databases. Office XP installs Msadomd.dll and other supporting files for MDX and data cubes. Microsoft provides OLE DB for
OLAP and the PivotTable Service to
enable Excel 2002 PivotTables to manipulate data cubes. MDX and PivotTable services are beyond the scope
of this book.
Mapping OLE DB Interfaces to ADO
You need to know the names and
relationships of OLE DB interfaces to ADO objects, because Microsoft
includes references to these interfaces in its technical and white papers on OLE DB and ADO. The OLE DB specification defines a set of interfaces to the
following objects:
·
DataSource objects provide a set of functions to identify a particular OLE DB data provider,
such as the Jet or SQL Server provider, and
determine whether the caller has the required security permissions for the
provider. If the provider is found and
authentication succeeds, a connection to the data source results.
·
Session objects provide an environment
for creating rowsets and isolating
transactions, especially with Microsoft Transaction Server (MTS), which runs
under Windows NT. The COM+ components of Windows 2000+ provide MTS services.
·
Command objects include sets of functions to handle queries, usually (but not necessarily)
in the form of SQL statements or names of stored procedures.
·
Rowset objects can be created directly from Session objects or as the result of
execution of Command objects. Rowset objects deliver data to the consumer
through the IRowset interface.
ADO maps the four OLE DB objects to the following three top-level Automation objects that are
familiar to Access programmers who've used ODBCDirect:
·
Connection objects combine OLE DB's DataSource and
Session
objects to specify the OLE DB data provider, establish a connection to the data
source, and isolate transactions to a
specific connection. The Execute method of the ADODB.Connection object can return a forward-only ADODB.Recordset object.
·
Command objects are directly analogous to
OLE DB's Command object. ADODB.Command objects accept an SQL statement, the
name of a table, or the name of a stored procedure. Command objects are used primarily for
executing SQL UPDATE, INSERT, DELETE, and
SQL Data Definition Language (DDL) queries that don't return records. You also
can return an ADODB.Recordset by executing an ADODB.Command object.
·
Recordset objects correspond to OLE DB's Rowset objects and have properties and
methods similar to Access 97's ODBCDirect Recordset. A Recordset is an in-memory image of a table or
a query result set.
When you open a new Access 2000+ database, Access automatically
adds a reference to the Microsoft ActiveX Data Objects 2.1 Library for VBA
programming. The ADODB prefix, the short name of the ADO type library, explicitly
identifies ADO objects that share object names
with DAO (Recordset) and DAO's ODBCDirect (Connection and
Recordset). For clarity, all ADO code examples in
this book use the ADODB prefix.
Tip
To make ADOX 2.x accessible to VBA, you must add a reference to
Microsoft ADO Ext. 2.x for DDL and Security to your application. Access 2000
doesn't add the ADOX reference automatically to new projects.
Comparing ADO
and DAO Objects
The ADO object
hierarchy, which can consist of nothing more than an ADODB.Connection object, is much simpler than the
collection-based object hierarchy of DAO. To obtain a scrollable, updatable Recordset (dynaset), you must open an ADODB.Recordset object on an active ADODB.Connection object.
Access VBA provides a DAO shortcut, Set
dbName= CurrentDB(), to bypass the first two collection
layers and open the current database,
but CurrentDB() isn't available in VBA code for other members of Office XP or
Visual Basic 6.0.
Note
Access VBA provides a similar ADO
shortcut, CurrentProject.Connection, which points to a default ADODB.Connection object with the Jet OLE DB Service
Provider for the current database. Unlike CurrentDB(), which is optional, you must use CurrentProject.Connection as the ADODB.Connection to the currently open database. If
you try to open a new ADODB.Connection to the current database, you receive
a runtime error stating that the database is locked.
Unlike DAO objects, most of which are members of collections,
you use the New reserved word with the Set instruction to create and the Close method, the SetObjectName=
Nothing, or
both statements to remove instances of ADODB.Connection, ADODB.Command, and
ADODB.Recordset objects independently of one another. The SetObjectName=
Nothing
instruction releases memory consumed by the object.
DAO supports a variety of Jet collections, such as Users and Groups, and
Jet SQL Data Definition Language (DDL)
operations that ADO 2.x alone
doesn't handle. ADOX 2.x defines Jet-specific collections and objects that aren't included in ADO
2.x.
Upgrading ADO
2.5 to Version 2.6
ADO 2.x in this chapter refers collectively to ADO
2.1, 2.5, and 2.6. Office XP and Windows 2000 Service Pack (SP) 1 install ADO 2.5 SP1, which includes type libraries
for ADO
2.0 and 2.1 for backward compatibility.
Installing the SQL Server 2000 Desktop Edition (MSDE2000) from the Office XP
distribution CD-ROM—or any other version of SQL Server 2000—upgrades ADO
2.5 to 2.6. Installing Windows XP upgrades ADO
2.x to 2.7 to support Intel's 64-bit Itanium processors. Upgrading from ADO
2.6 to 2.7 doesn't add new features or alter existing features.
Following are the new or altered ADO objects, properties, and methods in ADO
2.6:
·
Record objects can contain fields defined as Recordsets, Streams of binary or text data, and child records of hierarchical Recordset objects. Use of Record objects is beyond the scope of this
book.
·
Stream objects can send T-SQL FOR XML queries to SQL Server 2000 and return result sets in attribute-centric
XML format. Stream objects also are used with Record object to return binary data from
URL queries executed on file systems, Exchange 2000 Web Folders, and e-mail messages. The "Programming Stream Objects" section, near the end
of the lesson, provides a simple example of the use of a Stream object to return XML data from a FOR
XML T-SQL query to
a text box.
·
Command objects gain new CommandStream and
Dialect
properties to support Stream objects, and a NamedParameters property that applies to the
Parameters collection.
·
Group and
User
ADOX objects add a Properties collection that contains
Jet-specific Property objects. This book doesn't cover ADOX programming with
VBA, because ADOX applies only to Jet databases.
Tip
If you're interested in learning more about ADOX, open the VBA
Editor, type adox in the Ask a Question
text box, select the ADOX methods option, click See Also in the "ADOX
Methods" help page, and select
ADOX API Reference in the list.
Using the Object Browser to Display ADO
Properties, Methods, and Events
At this point in your ADO learning curve, a detailed list of properties, enumerations
of constant values, methods, and events
of ADO
components might appear premature. Understanding the capabilities and benefits of ADO, however, requires
familiarity with ADO's repertoire of
properties, methods, and events. To get
the most out of ADP and to program DAP
you must have a working knowledge of ADO programming
techniques.
DAO objects don't fire events; ADO
objects do. Visual Basic 4.0 and RDO
1.0 added an event model for RDO's rdoEnvironment, rdoConnection, and
rdoResultset objects. Visual Basic 5.0's RDO 2.0 increased the granularity
of the data-related events, providing developers with much finer control over
communication with SQL Server and other
client/server RDBMSs. Access objects offer fine-grained events, but don't
provide programmers with a lower-level event model for basic operations, such
as connecting to a database and
executing queries. Access 97's ODBCDirect offered an event model, but you
couldn't bind ODBCDirect Recordsets to forms. ADO
offers a complete event model, which is similar to that of RDO 2.0.
Object Browser is the most useful tool for becoming acquainted
with the properties, methods, and
events of ADODB objects. Object Browser also is the most convenient method for
obtaining help with the syntax and
usage of ADO
objects, methods, and events.
Note
The examples and tabular
list of properties, methods, and events
of ADO
objects in this and other related
chapters are for ADO 2.6. Objects,
methods, and events that are added by ADO
2.5+ are identified by the new in Access 2000 icon. If your .mdb or .adp file
has a reference to ADO 2.1 or 2.5, your
results for this chapter's examples might differ or fail to execute.
To use Object Browser with ADO objects, follow these steps:
1.
Open
in design mode one of the forms of ADOTest.mdb that you created in the
preceding sections, and then open the
VBA Editor for its code. Alternatively, open the sample ADOTest.mdb or
ADOTest.adp file.
2.
Press
F2 to open Object Browser.
3.
Select
ADODB in the library (upper) list.
4.
Select
one of the top-level components, such as Connection, in the Classes (left) pane.
5.
Select
a property, event, or method, such as Open, in the Members of 'ObjectName'(right) pane. A short-form version of the
syntax for the selected method or event appears in Object Browser's lower pane.
6.
Click
the Help button to open the help topic for the selected object, property,
method, or event. The Object Browser's help button opens
the online VBA help topic for the selected ADODB object, method, property, or event.
The See Also link leads to related help topics. If enabled, the Example link
opens a sample VBA subprocedure. The Applies To link opens a list of objects
that share the method, property, or event.
ADO type libraries also include enumerations (lists) of numeric
(usually Long) constant values with an ad prefix. These constant enumerations
are specific to one or more properties.
Tip
You can substitute the numeric value of enumerated constants for
the constant name in VBA, but doing so isn't considered a good programming
practice. Numeric values of the constants might change in subsequent ADO versions, causing
unexpected results when upgrading applications to a new ADO
release.
Transaction Isolation Levels
The ability to specify the transaction isolation level applies
only when you use the BeginTrans, CommitTrans, and
RollbackTrans to perform a transaction on a Connection object. If multiple database users
simultaneously execute transactions, your application should specify how it
responds to other transactions in-process.
The Connection.Mode Property
Unless you have a specific reason to
specify a particular ADODB.Connection.Mode value, the default adModeUnknown is adequate. The Jet OLE DB provider
defaults to adModeShareDenyNone. The Access Permissions list on the Advanced page of the Data Link properties page for SQLOLEDB
is disabled, but you can set the Mode property with code.
Tip
You often can improve performance of client/server
decision-support applications by opening the connection as read only (adModeRead). Modifying the structure of a database
with SQL's DDL usually requires exclusive access to the database (adModeShareExclusive).
The Connection.State Property
It's common to open and
close connections as needed to reduce the connection load on the database.
(Each open connection to a client/server database consumes a block of memory.)
In many cases, you must test whether the Connection object is open or closed before
applying the Close or Open method, or changing Connection property values, which are read-only when
the connection is open.
Errors Collection and Error Objects
The dependent Errors collection is a property of the Connection object, and if errors are encountered with any operation on the
connection, contains one or more Error objects. The Errors collection has one property, Count, which you test to determine whether
an error has occurred after executing a method call on Connection and
Recordset objects. A collection is required, because it's possible for an
object to generate several errors.
The Errors collection has two methods, Clear and Item. The Clear method deletes all current Error objects in the collection, resetting
the value of Count to 0. The Item method, which is the default method
of the Errors and other collections,
returns an object reference (pointer) to an Error object. The syntax for explicit and default use of the Item method is
Set errName = cnnName.Errors.Index({ strName|intIndex} )Set errName = cnnName.Errors({ strName|intIndex} )
Error objects have no methods or events.
The InfoMessage event of the Connection object, described in the "Connection
Events" section later in this chapter, fires when an Error object is added to the Errors collection and supplies a pointer to the newly added Error object.
Note
Unfortunately, not all RDBMS vendors implement SQLSTATE in the same way. If you test the SQLState property value, make sure to follow
the vendor-specific specifications for Condition and
Subcondition values.
Listing 1 is an example of code to open a Connection (cnnNwind) and
a Recordset (rstCusts) with conventional error handling; rstCusts supplies the Recordset property of the form. The
"Non-existent" table name generates a "Syntax error in FROM
clause" error in the Immediate window. The SetObjectName=Nothing statements in the error handler
recover the memory consumed by the objects.
Listing 1 VBA Code That Writes Error
Properties to the Immediate Window
Private Sub Form_Load
Dim cnnNwind As New ADODB.Connection
Dim rstCusts As New ADODB.Recordset
On Error GoTo CatchErrors
cnnNwind.Provider = "Microsoft.Jet.OLEDB.4.0"
cnnNwind.Open CurrentProject.Path & "\Northwind.mdb", "Admin"
With rstCusts
Set .ActiveConnection = cnnNwind
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Open "SELECT * FROM Non-existent"
End With
Set Me.Recordset = rstCusts
Exit Sub
CatchErrors:
Dim colErrors As Errors
Dim errNwind As Error
Set colErrors = cnnNwind.Errors
For Each errNwind In colErrors
Debug.Print "Description: " & errNwind.Description
Debug.Print "Native Error: " & errNwind.NativeError; ""
Debug.Print "SQL State: " & errNwind.SQLState
Debug.Print vbCrLf
Next errNwind
Set colErrors = Nothing
Set errNwind = Nothing
Set rstCusts = Nothing
Set cnnNwind = Nothing
Exit Sub
End Sub
Note
The frmErrors form of ADOTest.mdb and ADOTest.adp incorporates the preceding
code. Open the form to execute the code, change to Design view, open the VBA
editor, and press Ctrl+G to read the
error message in the Immediate window.
Connection
Methods
Only the Execute, Open, and
OpenSchema methods accept argument values. The OpenSchema method is of interest primarily for
creating database diagrams, data transformation for data warehouses and marts, and
online analytical processing (OLAP) applications.
The Connection.Open
and Connection.OpenSchema Methods
You must open a connection (before you can
execute a statement on it. The syntax of the Open method is
cnnName.Open [strConnect[, strUID[, strPwd, lngOptions]]]]
Alternatively, you can assign the connection string values to
the Connection object's Provider and
ConnectionString properties. The following example, similar to
that for the Recordset object examples early in
the lesson:
With cnnNwind
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = CurrentProject.Path & "\Northwind.mdb"
.Open
End With
In this case, all the information required to open a connection
to Northwind.mdb is provided as property values, so the Open method needs no argument values.
If you're creating a data dictionary or designing a generic
query processor for a client/server RDBMS, the OpenSchema method is likely to be of interest
to you. Otherwise, you might want to skip the details of the OpenSchema method, which is included here for
completeness. Schema information is called metadata,
data that describes the structure of data.
Tip
ADOX 2.x defines a Catalog object for Jet 4.0 databases that's more useful for Jet databases than the generic OpenSchema method, which is intended primarily
for use with client/server RDBMs. The Catalog object includes Groups, Users, Tables, Views, and
Procedures collections.
The Connection.Execute Method
The syntax of the Connection.Execute method to return a reference to a
forward-only ADODB.Recordset object is
Set rstName = cnnName.Execute (str Command, [lngRowsAffected[, lngOptions]])
Alternatively, you can use named arguments for all ADO
methods. Named arguments, however, require considerably more typing than
conventional comma-separated argument syntax. The named argument equivalent of
the preceding Set statement is
Set rstName = cnnName.Execute (Command:=strCommand, _
RowsAffected:=lngRowsAffected, Options:=lngOptions)
If strCommand doesn't return a Recordset, the syntax is
cnnName.Execute strCommand, [lngRowsAffected[, lngOptions]]
The value of strCommand can be an SQL statement, a table
name, the name of a stored procedure, or an arbitrary text string acceptable to
the data provider.
Tip
For best performance, specify a value for the lngOptions argument so the provider doesn't
need to interpret the statement to determine its type. The optional lngRowsAffected argument returns the number of rows
affected by an INSERT, UPDATE, or DELETE query; these types of queries return
a closed Recordset object. A SELECT query returns 0 to lngRowsAffected and
an open, forward-only Recordset with 0 or more rows. The value of lngRowsAffected is 0 for T-SQL updates queries and stored procedures that include the SET
NOCOUNT ON
statement.
Forward-only Recordset objects, created by what Microsoft
calls a firehose cursor, provide the best performance and minimum network traffic in a client/server environment.
However, forward-only Recordsets are limited to manipulation by VBA
code. If you set the RecordSource property of a form to a forward-only
Recordset, controls on the form
don't display field values.
Connection Events
Events are useful for trapping errors, eliminating the need to poll the values of
properties, such as State, and performing
asynchronous database operations. Microsoft modeled ADO's
Connection events on a combination of the event models for RDO 2.0's rdoEngine, rdoEnvironment, and
rdoConnection objects. (The ADODB.Connection object combines the functionality of
these three RDO objects.) To expose the ADODB.Connection events to your application, you must
use the WithEvents reserved word (without New) to declare the ADODB.
Connection object
in the Declarations section of a class or form module and then use a Set statement with New to create an instance of the object,
as shown in the following example:
Private WithEvents cnnName As ADODB.Connection
Private Sub Form_Load
Set cnnName = New ADODB.Connection
...
Code using the Connection object
...
cnnName.Close
End Sub
Tip
Take full advantage of ADO events in your
VBA data-handling code. Relatively few developers currently use event-handling
code in ordinary database front ends. DAO, which offer no event model, still
dominate today's spectrum of Windows data access techniques. ADO's event model initially will
be of primary interest to developers migrating from RDO to ADO.
Developers of data warehousing and OLAP
applications, which often involve very long-running queries, are most likely to
use events in conjunction with asynchronous query operations.
Using the ADODB.Command Object
The Command object is analogous to RDO's rdoQuery object. The primary purpose of the Command object is to execute parameterized
stored procedures, either in the form of the default temporary prepared
statements or persistent, precompiled T-SQL statements in SQL Server databases. MSDE and
SQL Server create temporary prepared statements that exist only for the
lifetime of the current client connection. Precompiled SQL statements are
procedures stored in the database file; their more common name is stored
procedure. When creating Recordset objects from ad hoc SQL statements,
the more efficient approach is to bypass the Command object and
use the Recordset.Open method.
Command Properties
The Command object has relatively few properties, many of which duplicate those of the Connection object. Like the Connection object, the Command object has its own provider-specific
Properties collection, which you can print to the Immediate window using statements similar to those for Command objects.
Tip
The Command object is required to take advantage
of ADO
2.5+'s new Stream object, which contains data in the form of a continuous
stream of binary data or text. Text streams often contain XML documents or
document fragments returned from SQL Server 2000 XML
AUTO queries. The
Microsoft OLE DB Provider for Internet Publishing (MSDAIPP) enables Connection, Recordset, Record, and
Stream
objects to bind to a URL and retrieve
data. Windows 2000's Internet Information Server (IIS) 5.0 adds the MSDAIPP
provider.
Tip
Always set the CommandType property to the appropriate adCmd... constant value. If you accept the
default adCmdUnknown value, the data provider must test the value of CommandText to determine whether it is the name
of a stored procedure, a table, or an SQL statement before executing the query.
If the targeted database contains a large number of objects, testing the CommandText value for each Command object you execute can significantly
reduce performance.
The initial execution of a prepared statement often is slower
than for a conventional SQL query because some data sources must compile, rather
than interpret, the statement. Thus, you should limit use of prepared
statements to parameterized queries in which the query is executed multiple
times with differing parameter values.
Parameters Collection
To supply and accept
parameter values, the Command object uses the Parameters collection, which is analogous to
the rdoParameters collection of the rdoQuery object and
similar to the DAO and ODBCDirect Parameters collections. ADODB.Parameters is independent of its parent, ADODB.Command, but you must associate the Parameters collection with a Command object before defining or using Parameter objects.
The Parameters collection has a read-only Long property, Count, an Item property that returns a Parameter object. The syntax for the Count and
Item
properties property is
lngNumParms = cmmName.Parameters.Count
prmParamName = cmmName.Parameters.Item(lngIndex)
You gain a performance improvement
for the initial execution of your stored procedure or query if you use the cmmName.CreateParameter method to predefine the required Parameter objects. The Refresh method makes a round-trip to the
server to retrieve the properties of each Parameter.
Parameter Object
One Parameter object must exist
in the Parameters collection for each parameter of the stored procedure, prepared
statement, or parameterized query. The syntax for getting and setting Parameter property values is
typPropValue = cmmName.Parameters({ strName|lngIndex} ).PropertyNamecmmName.Parameters({ strName|lngIndex} ).PropertyName = typPropValue
You don't need to use the Index property of the Parameters
collection; Index is the default property of Parameters.
The Type property has the largest collection of constants of any ADO
enumeration; you can review the entire list of data types by selecting the DataTypeEnum class in Object Browser. Most of the
data types aren't available to VBA programmers. In most cases, you only need to
choose among adChar (for String values), adInteger (for Long values), and adCurrency (for Currency values). You use the adDate data type to pass Date/Time
parameter values to Jet databases, but
not to most stored procedures. Stored procedures generally accept datetime parameter values as the adChar data type, with a format, such as
mm/dd/yyyy, acceptable to the RDBMS.
Note
The values for the Type property in the preceding table are valid for the Type property of the Field object, discussed later in the
chapter, except for those data types in which "Parameter objects
only" appears in the Description of Data Type column. The members of DataTypeEnum are designed to accommodate the widest possible range of desktop and client/server RDBMSs, but the ad... constant names are closely related to those for the field data types of
Microsoft SQL Server 2000 and MSDE,
which support Unicode strings.
For a complete list with descriptions of DataTypeEnum constants,
go to http://msdn.microsoft.com/library/psdk/dasdk/mdae8o19.htm.
The Parameter object has a single method, AppendChunk, which you use to append long text (adLongText) or long binary (adLongVarChar) Variant data as a parameter value. The
syntax of the AppendChunk method call is
cmmName.Parameters({ strName|lngIndex} ).AppendChunk = varChunk
The adParamLong flag of the prmName.Attributes property must be set to apply the AppendChunk method. If you call AppendChunk more than once on a single Parameter, the second and later calls append the current value of varChunk to the parameter value.
Command Methods
Command objects have only three methods: Cancel, CreateParameter and
Execute.
Executing Command.Cancel terminates an asynchronous command opened with the adAsyncConnect, adAsyncExecute, or adAsyncFetch option.
You must declare a ADODB.Parameter object, prmName, prior to executing CreateParameter. The syntax of the CreateParameter method call is
Set prmName = cmmName.CreateParameter [strName[, lngType[, _
lngDirection[, lngSize[, varValue]]]]]
cmmName.Parameters.Append prmName
The arguments of CreateParameter are optional only if you
subsequently set the required Parameter property values before executing the
Command.
For example, if you supply only the strName argument, you must set the remaining
properties, as in the following example:
Set prmName = cmmName.CreateParameter strName
cmmName.Parameters.Append prmName
With prmName
.Type = adChar
.Direction = adParamInput
.Size = Len(varValue)
.Value = varValue
End With
The syntax of the Command.Execute method is similar to that for the Connection.Execute method except for the argument list.
The following syntax is for Command objects that return Recordset objects:
Set rstName = cmmName.Execute([lngRowsAffected[, _
avarParameters[, lngOptions]]])
For Command objects that don't return rows, use this form:
cmmName.Execute [lngRowsAffected[, avarParameters[, lngOptions]]]
All the arguments of the Execute method are optional if you set the
required Command property values before applying the Execute method. Listing 2
later in this lesson gives an example of the use of the Command.Execute method without arguments.
Tip
Presetting all property values of the Command object, rather than supplying
argument values to the Execute method, makes your VBA code easier
for others to comprehend.
Like the Connection.Execute method, the returned value of lngRowsAffected is 0 for SELECT and
DDL queries and the number of rows
modified by execution of INSERT, UPDATE, and
DELETE
queries. (For SQL Server, lngRowsAffected is 0 if the SQL statement includes SET
NOCOUNT ON.) The avarParameters argument is an optional Variant array of parameter values. Using the Parameters collection is a better practice than
using the avarParameters argument because output parameters
don't return correct values to the array. For lngOptions
constant values.
Code to Pass Parameter Values to a Stored Procedure
Most stored procedures that return Recordset objects require input parameters to
supply values to WHERE clause criteria to limit the number of rows returned.
The code of Listing 2 executes a simple SQL Server 2000 stored procedure with a
Command
object. The stored procedure, whose SQL statement follows, returns the
ShippedDate and OrderID columns of the
Orders table, the Subtotal column of the Order Subtotals view, and a calculated Year value. The stored
procedure returns rows for values of the OrderDate field between strBegDate and
strEndDate.
ALTER PROCEDURE "Sales by Year"
@Beginning_Date datetime,
@Ending_Date datetime
AS SELECT Orders.ShippedDate, Orders.OrderID,
"Order Subtotals".Subtotal,
DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals"
ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
Listing 2 Code Using a Command Object to Execute a Parameterized Stored Procedure
Option Explicit
Option Compare Database
Private cnnOrders As New ADODB.Connection
Private cmmOrders As New ADODB.Command
Private prmBegDate As New ADODB.Parameter
Private prmEndDate As New ADODB.Parameter
Private rstOrders As New ADODB.Recordset
Private Sub Form_Load()
Dim strBegDate As String
Dim strEndDate As String
Dim strFile As String
strBegDate = "1/1/1997"
strEndDate = "12/31/1997"
strFile = CurrentProject.Path & "Orders.rst"
'Specify the OLE DB provider and open the connection
With cnnOrders
.Provider = "SQLOLEDB.1"
On Error Resume Next
.Open "Data Source=(local);" & _
"UID=sa;PWD=;Initial Catalog=NorthwindCS"
If Err.Number Then
.Open "Data Source=(local);" & _
"Integrated Security=SSPI;Initial Catalog=NorthwindCS"
End if
On Error GoTo 0
End With
With cmmOrders
'Create and append the BeginningDate parameter
Set prmBegDate = .CreateParameter("BegDate", adChar, _ adParamInput, Len(strBegDate), strBegDate)
.Parameters.Append prmBegDate
'Create and append the endingDate parameter
Set prmEndDate = .CreateParameter("EndDate", adChar, _ adParamInput, Len(strEndDate), strEndDate)
.Parameters.Append prmEndDate
Set .ActiveConnection = cnnOrders
'Specify a stored procedure
.CommandType = adCmdStoredProc
'Brackets must surround stored procedure names with spaces
.CommandText = "[Sales By Year]"
'Receive the Recordset
Set rstOrders = .Execute 'returns a "firehose" Recordset
End With
With rstOrders
'Save (persist) the forward-only Recordset to a file
On Error Resume Next
'Delete the file, if it exists
Kill strFile
On Error GoTo 0
.Save strFile
.Close
.Open strFile, "Provider=MSPersist", , , adCmdFile
End With
'Assign rstOrders to the Recordset of the form
Set Me.Recordset = rstOrders
Me.txtShippedDate.ControlSource = "ShippedDate"
Me.txtOrderID.ControlSource = "OrderID"
Me.txtSubtotal.ControlSource = "Subtotal"
Me.txtYear.ControlSource = "Year"
End Sub
Caution
When used in ADO code, you must enclose names of stored
procedures and views having spaces with
square brackets. Including spaces in database object names, especially in
client/server environments, isn't a recommended practice. Microsoft developers
insist on adding spaces in names of views and
stored procedures, perhaps because SQL Server 2000 supports this dubious
feature. Use underscores to make object names more readable if necessary.
Note
The code of Listing 2 uses an ADO
2+ feature, persisted (saved) Recordset objects. Stored procedures return
forward-only ("firehose") Recordset objects, which you can't assign to
the Recordset property of a form. To create a Recordset with a cursor acceptable to Access
forms, you must persist the Recordset as a file and then close and
reopen the Recordset with the MSPersist OLE DB provider as the ActiveConnection property value. The "Recordset
Methods" section, later in the lesson, provides the complete syntax for
the Save
and Open methods of the Recordset object.
Understanding the
ADODB.Recordset
Object
Creating, viewing, and
updating Recordset objects is the ultimate objective of most Access database front
ends. Opening an independent ADODB.Recordset object offers a myriad of cursor,
locking, and other options. You must
explicitly open a Recordset with a scrollable cursor if you want to use code to
create the Recordset for assignment to the Form.Recordset property. Unlike Jet and ODBCDirect Recordset objects, ADODB.Recordset objects expose a number of events
that are especially useful for validating Recordset updates. Microsoft modeled ADODB.Recordset events on the event repertoire of
the RDO 2.0 rdoResultset object.
Recordset Properties
Microsoft attempted to make ADODB.Recordset objects backward compatible with DAO.Recordset objects to minimize the amount of
code you must change to migrate existing applications from DAO to ADO. Unfortunately, the attempt at backward
compatibility for code-intensive database applications didn't fully succeed.
You must make substantial changes in DAO code to accommodate ADO's
updated Recordset object. Thus, most Access developers choose ADO
for new Access front-end applications and
stick with DAO for existing projects.
ADODB.Recordset objects have substantially fewer
properties than DAO.Recordset objects have. The standard
properties of ADODB.Recordset objects are those that are supported
by the most common OLE DB data providers for relational databases.
The most obvious omission in the
preceding table is the DAO.Recordset NoMatch property value used to test whether
applying one of the DAO.Recordset.Find... methods or the DAO.Recordset.Seek method succeeds. The new ADODB.Recordset.Find method, listed in the
"Recordset Methods" section later in this chapter, substitutes for
DAO's FindFirst, FindNext, FindPrevious, and
FindLast
methods. The Find method uses the EOF property value for testing the
existence of one or more records matching the Find criteria.
Another omission in the ADODB.Recordset object's preceding property list is
the PercentPosition property. The workaround, however, is easy:
rstName.AbsolutePostion = Int(intPercentPosition * rstName.RecordCount / 100)
Fields Collection and Field Objects
Like DAO's Fields collection, ADO's dependent Fields collection is a property of the Recordset object, making the columns of the Recordset accessible to VBA code and bound controls.
The Fields
collection has one property, Count, and
only two methods, Item and Refresh. You can't append new Field objects to the Fields collection, unless you're creating a
persisted Recordset from scratch or you use ADOX's ALTER
TABLE DDL command
to add a new field.
All but one (Value) of the property values of Field objects are read-only, because the
values of the Field properties are derived from the database schema. The Value property is read-only in
forward-only Recordsets and Recordsets opened with read-only locking.
Value is the default property of the Field object, but a good programming
practice is to set and return field
values by explicit use of the Value property name in VBA code. In most
cases, using varName=
rstName.Fields(n).Value instead of varName=
rstName.Fields(n) results in a slight performance improvement.
The Field object has two methods, AppendChunk and
GetChunk,
which are applicable only to fields of various long binary data types,
indicated by an adFldLong flag in the Attributes property of the field. The syntax
for the AppendChunk method call, which writes Variant data to a long binary field (fldName), is fldName.AppendChunk varData
Note
ADO 2.x doesn't support the Access OLE
Object field data type, which adds a proprietary object wrapper around the data
(such as a bitmap) to identify the OLE server that created the object (for
bitmaps, usually Windows Paint).
The GetChunk method enables
you to read long binary data in blocks of the size you specify. Following is
the syntax for the GetChunk method:
varName = fldName.GetChunk(lngSize)
A common practice is to place AppendChunk and
GetChunk
method calls within Do Until...Loop structures to break up the long binary value into chunks of
manageable size. In the case of the GetChunk method, if you set the value of lngSize to less than the value of the
field's ActualSize property, the first GetChunk call retrieves lngSize bytes. Successive GetChunk calls retrieve lngSize bytes beginning at the next byte
after the end of the preceding call. If the remaining number of bytes is less
than lngSize, only the remaining bytes appear in varName. After you retrieve the field's
bytes, or if the field is empty, GetChunk returns Null.
Note
Changing the position of the record pointer of the field's Recordset resets GetChunk's byte pointer. Accessing a
different Recordset and moving its record
pointer doesn't affect the other Recordset's GetChunk record pointer.
Recordset Methods
ADODB.Recordset methods are an amalgam of the DAO.Recordset and
rdoResultset methods. OLE DB data providers aren't required to support all
the methods of the Recordset object. If you don't know which methods the data
provider supports, you must use the Supports method with the appropriate constant
from CursorOptionEnum. The "Code to
Pass Parameter Values to a Stored Procedure" section, earlier
in the chapter, illustrates use of the Save and
Open
methods with persisted Recordsets of the ADTG type.
Tip
To change the value of one or more fields of the current record
of an ADODB.Recordset object, execute rstName.Fields(n).Value=
varValue for
each field whose value you want to change and
then execute rstName.Update. ADODB.Recordset objects don't support the Edit method.
To improve the performance of Recordset objects opened on Connection objects, set the required property
values of the Recordset object and then
use a named argument to specify the intOptions value of the Open method, as in rstName.
Open Options:=adCmdText. This syntax is easier to read and less prone to error than the alternative,
rstName.Open , ,
, , adCmdText.
Unfortunately, the syntax for the ADODB.Recordset.Seek method isn't even close to being
backward-compatible with the DAO.Recordset.Seek method.
Tip
Use the Find method for searches unless you are working with a table
having an extremely large number of records. Find takes advantage of index(es), if present, but Find's search algorithm isn't quite as
efficient as Seek's. You'll probably encounter the threshold for
considering substituting Seek for Find in the range of 500,000 to 1,000,000
records. Tests on a large version the Oakmont.mdb Jet and Oakmont SQL Server Students table (50,000) rows show
imperceptible performance differences between Seek and
Find
operations.
Recordset Events
Recordset events are new to users of DAO.
Taking Advantage
of Disconnected Recordsets
If you set the value of the Recordset's LockEdits property to adBatchOptimistic and
the CursorType property to adKeyset or adStatic, you create a batch-type Recordset object that you can disconnect from
the data source. You can then edit the Recordset object offline with a client-side
cursor, reopen the Connection object, and send the updates to the data source over the new
connection. A Recordset without an active connection is called a disconnected
Recordset. The advantage of a disconnected Recordset is that you
eliminate the need for an active server connection during extended editing
sessions.
Note
Unfortunately, you can't assign a disconnected Recordset to the Recordset property of a form or subform and take advantage of batch updates. Bound
forms require an active connection to the database. You must write VBA code to
handle updating, adding, and deleting
records.
Batch updates with disconnected Recordsets are stateless and resemble the interaction of Web browsers and servers when displaying conventional Web
pages. The term stateless means that the
current interaction between the client application and the server isn't dependent on the outcome of previous
interactions. For example, you can make local updates to a disconnected
Recordset, go to lunch, make additional updates as needed, and then send the entire batch to the server.
A properly designed batch update application lets you close the application or
shut down the client computer, and then
resume the updating process when you restart the application.
Tip
Disconnected Recordsets minimize the effect of MSDE
"five-user tuning" on the performance of Access online transaction
processing (OLTP) applications. Batch updates execute very quickly, so most
user connections remain open for a second or less.
Transaction processing with stored procedures or T-SQL
statements that incorporate BEGIN TRANS...COMMIT
TRANS...ROLLBACK TRANS
statements are the better choice for OLTP operations on multiple tables, such
as order-entry systems. It's possible for batch updates to succeed partially,
which might result in a missing line item. You can use the Errors collection to analyze and potentially correct such problems, but
doing so requires high-level VBA coding skills.
The Basics of
Disconnecting and Reconnecting Recordsets
Following is an example of VBA pseudocode that creates and
operates on a disconnected Recordset and then uses
the UpdateBatch method to persist the changes in the data source:
Set rstName = New ADODB.Recordset
With rstName
.ActiveConnection = cnnName
.CursorType = adKeyset
.CursorLocation = adUseClient
.LockEdits = adBatchOptimistic
.Open "SELECT * FROM TableName WHERE Criteria", Options:=adCmdText
Set .ActiveConnection = Nothing 'Disconnect the Recordset
'Close the connection to the server, if desired
'Edit the field values of multiple records here
'You also can append and delete records
'Reopen the server connection, if closed
Set .ActiveConnection = cnnName
.UpdateBatch 'Send all changes to the data source
End With
rstName.Close
If calling the UpdateBatch method causes conflicts with other users'modifications to the
underlying table(s), you receive a trappable error and the Errors collection contains Error object(s) that identify the
conflict(s). Unlike transactions, which require all attempted modifications to
succeed or all to be rolled back, Recordset batch modifications that don't cause
conflicts are made permanent in the data source.
A Sample Batch Update Application
The frmBatchUpdate form of the ADOTest.mdb application and ADOTest.adp project demonstrates the effectiveness of batch
updates with MSDE. For example, you can edit data, persist
the edited disconnected Recordset as an ADTG or XML file, and close the form
(or Access), and then reopen the form and submit the changes to the server. A
subform, sbfBatchUpdate, which is similar to the frmADO_Jet and frmADO_MSDE forms you created early in
the chapter, displays the original and
updated data. The subform is read-only; VBA code simulates user updates to the
data. The example also demonstrates how to use VBA code to display the XML
representation of a Recordset object in Internet Explorer (IE) 5+.
If you update the local copy of the Recordset and don't send the changes to the server, you
receive a message reminding you that changes are pending when you close the
form. If you don't save the changes to the server and
reopen the form, a message asks if you want to send the changes to the server
before proceeding.
VBA Code in the frmBatchUpdate Class Module
The VBA code of the event-handling and
supporting subprocedures of the frmBatchUpdate Class Module illustrates how to
program many of the ADO properties and methods described in the preceding
sections devoted to the Connection and
Recordset objects. The Command object isn't used in this example,
because the form opens Recordset objects on a temporary Connection object or from a copy of a Recordset persisted to a local file in ADTG
format.
The Form_Load Event Handler
Listing 3 shows the VBA code for the Form_Load event handler. The first operation uses the VBA Dir function to determine whether the
Batch.rst file exists; if so, response to the message specified by the MsgBox function determines whether existing
updates are processed by the cmdUpdate_Click subprocedure or discarded.
Listing 3 Code for Saving the Initial Recordset Object
Private Sub Form_Load()
'Open the connection, and create and display the Recordset
blnUseJet = False 'Set True to use the Jet provider
'Test for presence of the saved Recordset
If Dir(CurrentProject.Path & "\Batch.rst") <> "" Then
'File is present so updates are pending
If MsgBox("Do you want to send your changes to the server?", vbQuestion + _ vbYesNo,
"Updates Are Pending for the Server") = vbYes Then
Call cmdUpdate_Click
Exit Sub
Else
Kill CurrentProject.Path & "\Batch.rst"
End If
End If
'Create a Form object variable for the subform
Set sbfBatch = Forms!frmBatchUpdate!sbfBatchUpdate.Form
Me.cmdBulkUpdate.SetFocus
Me.cmdUpdate.Enabled = False
Me.cmdOpenXML.Enabled = False
'Open a connection to the server
Call OpenConnection
'Create a Recordset for Batch Updates
strSQL = "SELECT CustomerID, CompanyName, Address, City, Region,
PostalCode, Country FROM Customers"
With rstBatch
Set .ActiveConnection = cnnBatch
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open strSQL
'Save the Recordset to a file
.Save CurrentProject.Path & "\Batch.rst", adPersistADTG
'Save an XML version
On Error Resume Next
Kill CurrentProject.Path & "\Batch.xml"
.Save CurrentProject.Path & "\Batch.xml", adPersistXML
On Error GoTo 0
Me.cmdOpenXML.Enabled = True
'Disconnect the Recordset
Set .ActiveConnection = Nothing
If .Fields("Region").Value = "123" Then Me.cmdBulkUpdate.Caption = "Restore Disconnected Recordset"
Else
Me.cmdBulkUpdate.Caption = "Update Disconnected Recordset"
End If
End With
'Destroy the connection
cnnBatch.Close
Set cnnBatch = Nothing
'Open a local Recordset from the saved file
Call OpenRstFromFile
'Delete the source of the file Recordset
Kill CurrentProject.Path & "\Batch.rst"
Me.Caption = "Datasheet Contains Values from Server (Disconnected Recordset)"
End Sub
Note
In a real-world application, you probably wouldn't delete a saved
Recordset that contains updates. Instead of deleting the file with a Kill instruction, you would open the
saved Recordset to permit continued editing.
The Set sbfBatch=
Forms!frmBatchUpdate!sbfBatchUpdate.Form statement creates a Form object for the subform, so you can
set property values for the sbfBatchUpdate subform by code of the
frmBatchUpdate form in the OpenRstFromFile subprocedure. Combining the VBA code
for forms and subforms in a single
Class Module makes the code more readable.
For more information on the strange syntax to point to another Form or Report object, see
"Referring to Access Objects with VBA".
After disabling the Send Updates to Server and Open Batch.xml in IE 5+ buttons, the code
calls the OpenConnection subprocedure to create a temporary Connection object, creates a Recordset object with batch-optimistic
locking, saves the Recordset to Batch.rst and
Batch.xml, and disconnects the Recordset from the connection with the Set
.ActiveConnection=Nothing statement. Finally the code closes the Connection, releases it from memory, calls the OpenRstFromFile subprocedure, and deletes the Batch.rst file.
The OpenConnection Subprocedure
The OpenConnection subprocedure (see Listing 4) accommodates a Jet database by setting the
value of blnUseJet to True in the Form_Load event handler. For SQL Server 2000
with SQL Server security enabled, the connection is made with the sa logon ID (UID=sa) and
no password. (If you've secured the sa account, add
the password for the account to PWD=.) If SQL Server security isn't
enabled, an error occurs, and the code
tries integrated Windows security.
Listing 4 Connecting to a Jet Database or
Use SQL Server or Integrated Windows Security to Connect to the Local MSDE
Instance
Private Sub OpenConnection()
'Specify the OLE DB provider and open the connection
With cnnBatch
If blnUseJet Then
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Path & "\Northwind.mdb", "Admin"
Else
On Error Resume Next
'Try SQL Server security first
.Open "Provider=SQLOLEDB.1;Data Source=(local);" & _
"UID=sa;PWD=;Initial Catalog=NorthwindCS"
If Err.Number Then
Err.Clear
On Error GoTo 0
'Now try integrated Windows security
.Open "Provider=SQLOLEDB.1;Data Source=(local);" & _
"Integrated Security=SSPI;Initial Catalog=NorthwindCS"
End If
End If
End With
End Sub
The OpenRstFromFile Subprocedure
The code for the OpenRstFromFile Subprocedure derives from that behind the frmADO_Jet and frmADO_MSDE forms. The primary difference
in the code of Listing 5 is that the Recordset.Open method specifies the temporary
Batch.rst file as its data source.
Listing 5 Opening a Saved Recordset Object and
Assigning It to the Recordset Property of the Subform
Private Sub OpenRstFromFile()
If rstBatch.State = adStateOpen Then
rstBatch.Close
End If
rstBatch.Open CurrentProject.Path & "\Batch.rst", , adOpenStatic, _
adLockBatchOptimistic, adCmdFile
With sbfBatch
'Assign rstBatch as the Recordset for the subform
Set .Recordset = rstBatch
.UniqueTable = "Customers"
.txtCustomerID.ControlSource = "CustomerID"
.txtCompanyName.ControlSource = "CompanyName"
.txtAddress.ControlSource = "Address"
.txtCity.ControlSource = "City"
.txtRegion.ControlSource = "Region"
.txtPostalCode.ControlSource = "PostalCode"
.txtCountry.ControlSource = "Country"
End With
End Sub
The cmdBulkUpdate Event Handler
Clicking the Update Disconnected Recordset/Restore Disconnected Recordset
button executes the cmdBulkUpdate event-handler (see Listing 6). The Set
sbfBatch.Recordset=Nothing statement prevents flashing of the subform during edits
performed in the Do While Not
.EOF...Loop
process. This loop traverses the Recordset and
changes the values of unused Region cells from NULL to 123 or vice versa. After the loop
completes, the form hooks back up to the edited Recordset. The call to the Form_Load subprocedure displays the updated
Customers table fields in the subform.
Note
Real-world applications use an unbound form and unbound text boxes to edit the Recordset.
The form requires command buttons to navigate the Recordset by invoking Move... methods. The event handler for an
Update Record button makes the changes to the field values of the local
Recordset.
Listing 6 The cmdBulkUpdate Event Handler Uses a Loop to Emulate Multiple Recordset Editing Operations
Private Sub cmdBulkUpdate_Click()
Dim blnUpdate As Boolean
Dim strCapSuffix As String
'Housekeeping for form and button captions
strCapSuffix = " While Disconnected (Updates Are Pending)"
IfMe.cmdBulkUpdate.Caption = "Update Disconnected Recordset" Then
Me.Caption = "Changing Empty Region Values to 123" & strCapSuffix
blnUpdate = True
Me.cmdBulkUpdate.Caption = "Restore Disconnected Recordset"
Else
Me.Caption = "Returning Region Values from 123 to Null" & strCapSuffix
blnUpdate = False
Me.cmdBulkUpdate.Caption = "Update Disconnected Recordset"
End If
'If you don't execute the following instruction, the subform
'datasheet can cause flutter vertigo during updates
Set sbfBatch.Recordset = Nothing
'Set the Field variable (improves performance)
Set fldRegion = rstBatch.Fields("Region")
'Now update or restore Region values
With rstBatch
.MoveFirst
Do While Not .EOF
If blnUpdate Then
If IsNull(fldRegion.Value) Then
fldRegion.Value = "123"
End If
Else
'Restore the original Null value
If fldRegion.Value = "123" Then
fldRegion.Value = Null
End If
End If
.MoveNext
Loop
On Error Resume Next
'For safety
Kill CurrentProject.Path & "\Batch.rst"
On Error GoTo 0
.Save CurrentProject.Path & "\Batch.rst", adPersistADTG
End With
'Now restore the subform's Recordset property
Set sbfBatch.Recordset = rstBatch
Me.cmdUpdate.Enabled = True
End Sub
Tip
Create a Field variable (fldRegion), instead of using a Recordset.Fields (strFieldName).Value=
varValue
instruction. Specifying a Field variable improves performance,
especially if the Recordset has many fields.
The cmdUpdate Event Handler
Clicking the Send Updates to Server button executes
the cmdUpdate event handler and the UpdateBatch method to update the server tables
(see Listing 7). Before executing the update, Debug.Print statements record the OriginalValue and
Value
property values for the first row in the Immediate
window.
Listing 7 Updating the Server
Tables Reconnects the Recordset to the Data Source, Executes the UpdateBatch
Method, and Closes the Connection
Private Sub cmdUpdate_Click()
'Recreate the connection
Call OpenConnection
'Reopen the Recordset from the file
With rstBatch
If .State = adStateOpen Then
.Close
End If
Set rstBatch.ActiveConnection = cnnBatch
.Open CurrentProject.Path & "\Batch.rst", , adOpenStatic, _
adLockBatchOptimistic, adCmdFile
'To demonstrate these two properties
Debug.Print "Original Value: " & .Fields("Region").OriginalValue Debug.Print "Updated Value: " & .Fields("Region").Value
'Send the updates to the server
.UpdateBatch
.Close
End With
'Clean up
Set rstBatch = Nothing
cnnBatch.Close
Set cnnBatch = Nothing
Kill CurrentProject.Path & "\Batch.rst"
'Load the subform datasheet from the server
Call Form_Load
Me.Caption = "Updated Values Retrieved from Server"
End Sub
The cmdOpenXML Event Handler
The cmdOpenXML event handler for the Open Batch.rst in IE 5+ button demonstrates use of
the VBA Shell function to launch another application (see Listing 8).
The argument of the Shell function is identical to the
instruction you type in the Run dialog's Open text box to launch an application
manually. If successful, the Shell function returns the task ID value
of the running application; if not, the function returns an empty Variant value.
Listing 8 Opening a Persistent
Recordset Object Saved As an XML File in IE 5+
Private Sub cmdOpenXML_Click()
'Launch IE 5+ with Batch.xml as the source URL
DimstrURL As String
Dim strShell As String
Dim varShell As Variant
strURL = "file://" & CurrentProject.Path & "\Batch.xml"
strShell = "\Program Files\Internet Explorer\Iexplore.exe " & strURL
varShell = Shell(strShell, vbNormalFocus)
If IsEmpty(varShell) Then
MsgBox "Can't open Internet Explorer", vbOKOnly + vbExclamation, _
"Unable to Display Batch.xml"
End If
End Sub
The Form_Unload Event Handler
Variables in form Class Modules disappear (go out of scope) when the
form closes.
However, it's a good programming practice to "clean up" all object
variables before closing a form. In addition to cleanup operations, this event
handler (see Listing 9) detects the presence of unsent updates in Batch.rst.
Setting the intCancel argument to True cancels the unload operation.
Listing 9 The Form_Unload
Event Handler Checks for Unsent Updates and, If the User Clicks Yes in the Message
Box, Closes Open Objects and Sets Them
to Nothing
Private Sub Form_Unload(intCancel As Integer)
'Check for pending updates before unloading
If Dir(CurrentProject.Path & "\Batch.rst") <> "" Then
If MsgBox("Are you sure you want to quit now?", vbQuestion + vbYesNo, _ "Updates Are Pending for the Server") = vbNo Then
intCancel = True
Exit Sub
End If
End If
'Clean up objects
If rstBatch.State = adStateOpen Then
rstBatch.Close
End If
Set rstBatch = Nothing
If cnnBatch.State = adStateOpen Then
cnnBatch.Close
End If
Set cnnBatch = Nothing
'If you don't execute the following instruction,
'you receive an error when opening the form
Set sbfBatch.Recordset = Nothing
End Sub
Tip
Unlike Visual Basic forms, values you assign with VBA code to
Access Form, Report, and Control objects persist after closing the
object and exiting the Access
application. In some cases, reopening the object results in
an error message. Executing the Set
sbfBatch.Recordset=Nothing instruction before closing the form and its subform prevents the possibility of an error on
reopening the form, because the source value of the Recordset property isn't present before the Form_Load event handler executes.
Programming Stream Objects
For
Access programmers, Stream objects primarily are of interest for
returning attribute-centric XML data documents from SQL Server 2000. The T-SQL
statement for the query must terminate with the FOR XML AUTO
or FOR XML RAW option. Both options return a
well-formed XML document using
Microsoft's xml-sql schema. Unlike the .xml files saved from Recordset
objects with the adPersistXML option, the stream doesn't include the
schema elements. Like the rowset schema, xml-sql
isn't compatible with Access 2000's native XML schema. SQL Server HTTP template
queries, which return HTML tables to Web browsers from FOR
XML AUTO queries, require the xml-sql
schema.
Exploring the VBA Code to Create a Stream Object
Most of the event handlers and subprocedures used by the VBA code for
the frmStream form derive from those of the frmBatch form described earlier.
The two important code elements behind frmStream are the Declarations section,
which declares the ADODB.Command and
ADODB.Stream object variables, and
constants for the currently allowable GUID values of the Command.Dialect property, and the cmdExecute_Click event handler (see Listing 10).
Listing 10 Creating a Stream Object from
an SQL Server FOR
XML AUTO Query and Displaying the Stream in a Text Box
Option Compare Database
Option Explicit
Private cnnStream As New ADODB.Connection
Private cmmStream As New ADODB.Command
Private stmQuery As ADODB.Stream
'GUID constants for Stream.Dialect
Private Const DBGUID_DEFAULT As String = _
"{ C8B521FB-5CF3-11CE-ADE5-00AA0044773D} "Private Const DBGUID_SQL As String = _
"{ C8B522D7-5CF3-11CE-ADE5-00AA0044773D} "Private Const DBGUID_MSSQLXML As String = _
"{ 5D531CB2-E6Ed-11D2-B252-00C04F681B71} "Private Const DBGUID_XPATH As String = _
"{ ec2a4293-e898-11d2-b1b7-00c04f680c56} "
'Constants for XML query prefix and suffix
Private Const strXML_SQLPrefix As String = _
"<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & vbCrLf & "<sql:query>"
Private Const strXML_SQLSuffix As String = "</sql:query>" & vbCrLf & "</ROOT>"
Private Sub cmdExecute_Click()
'Use Command and Stream objects to return XML as text
Dim strXMLQuery As String
Dim strXML As String
Dim lngCtr As Long
On Error GoTo errGetXMLStream
strXMLQuery = Me.txtQuery.Value
'Add the XML namespace and <ROOT...> and </ROOT> tags to the query text
strXMLQuery = strXML_SQLPrefix & vbCrLf & strXMLQuery & vbCrLf &_
strXML_SQLSuffix
'Display the CommandText property value
Me.txtXMLQuery.Value = strXMLQuery,
DoEvents
'Create a new Stream for each execution
Set stmQuery = New ADODB.Stream
stmQuery.Open
'Set and execute the command to return a stream
With cmmStream
Set .ActiveConnection = cnnStream
'Query text is used here, not an input stream
.CommandText = strXMLQuery
'Specify an SQL Server FOR XML query
.Dialect = DBGUID_MSSQLXML
'Specify the stream to receive the output
.Properties("Output Stream") = stmQuery .Execute , , adExecuteStream
End With
'Reset the stream position
stmQuery.Position = 0
'Save the stream to a local file
stmQuery.SaveToFile CurrentProject.Path & "\Stream.xml", adSaveCreateOverWrite
cmdOpenXML.Enabled = True
'Extract the text from the stream
strXML = stmQuery.ReadText
'Make the XML more readable with line feeds, if it isn't too long
If Len(strXML) < 15000 Then
Me.txtXML.Value = Replace(strXML,"><",">" &
vbCrLf &
"<")
Else
If Len(strXML) > 32000 Then
'Limit the display to capacity of text box
Me.txtXML.Value = Left$(strXML, 30000)
Else
Me.txtXML.Value = strXML
End If
End If
Exit Sub
errGetXMLStream:
MsgBox Err.Description, vbOKOnly + vbExclamation, "Error Returning XML Stream"
Exit Sub
End Sub
This form only uses the DBGUID_MSSQLXML constant; the other three GUID constants are for reference only. ADO 2.6's type library doesn't have a "DialectGUIDEnum" or similar enumeration, so you
must declare at least the DBGUID_MSSQLXML constant to request SQL Server to
return XML data documents in the xml-sql dialect. Comments in the body of the
code of the cmdExecute_Click event handler describe the purpose
of each, Stream-related statement.
In the Real
World—Why Learn ADO Programming?
"Everything has to be somewhere" is a popular
corollary of the Law of Conservation of Matter. So, just about everything you
need to know about ADO 2.x is
concentrated in this chapter. The problem with this "laundry list"
approach to describing a set of data-related objects is that readers are likely
to doze off in mid-chapter. If you've gotten this far (and have at least scanned the intervening code and tables), you probably surmised that ADO is more than just a replacement for
DAO—it's a relatively new and expanding
approach to database connectivity.
The most important reason to become an accomplished ADO
programmer is to create Web-based database applications. Microsoft designed OLE
DB and ADO
expressly for HTML- and XML-based
applications, such as DAP. You can use VBScript or JScript (Microsoft's variant
of JavaScript) to open and manipulate ADO Connection, Command, and
Recordset objects on Web pages. With DAO, you're stuck with conventional
Access applications that require users to have a copy of Office XP or you to
have the Microsoft Office XP Developer Edition (MOD 10) so you can supply
runtime versions of your Access 2000 applications.
Another incentive for becoming ADO-proficient
is migrating from Jet 4.0 to ADP. When SQL Server marketing honchos say that
SQL Server is Microsoft's "strategic database direction," believe
them. Jet still isn't dead, but the handwriting is on the wall; ultimately SQL
Server will replace Jet in all but the most trivial database applications. You
can expect SQL Server 2000+, OLE DB, and
ADO
to receive all future database development effort by Microsoft. Microsoft's VB
6 framework includes ADOVB 6 but don't
bother searching for "DAOVB 6" in Visual StudioVB 6 or OfficeVB 6.
SQL Server 2000 (including MSDE) now dominates the "sweet spot" of
the client/server RDBMS market—small- to mid-size firms—plus division-level and Web site RDBMS installations of the
Fortune 1000.
The ultimate answer to "Why learn ADO programming?", however,
is "Microsoft wants you to use ADO."
Like the proverbial one-ton gorilla, Microsoft usually gets what it wants.
