clientservervblssn1
Database Access  
 
Getting Acquainted with VBA 6

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} ).PropertyName
cmmName.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.

 

My Quia activities and quizzes
Client/Server Development and Remote Data Lesson 1 Quiz: session quiz1clientservervb
https://www.quia.com/quiz/574809.html
Lesson 1 Quiz
Useful links
Last updated  2008/09/28 00:54:31 PDTHits  973