Multitier Architecture Part II

Copyright 1994-97 Paul D. Sheriff & Associates

All Rights Reserved Worldwide, Reproduction is strictly prohibited.

Creating reusable classes that you can use in every database application is a great productivity tool. Of course, you must take some time up front to develop these; the payoff will come on your next application. There are many examples of reusable classes you can create. In this course, you will learn about a couple of them.

A data access class is a wrapper around all of the things you may do to a base table. Adding, editing, deleting and retrieving records will all become methods that you invoke. You can even add functionality such as specifying the WHERE clause and the ORDER BY clause. Once you have created one of these classes, you will never have SQL statements all over your source code again. Instead, you will always be instantiating an object, setting some properties, and invoking some methods. You will find that by doing this, you will greatly speed up your development time, cut down the number of bugs, and greatly reduce the maintenance time.

Reusable Classes

The BARK sample application uses many different classes. There will be classes that are generic that can be used in many applications, and there are others that are specific to the BARK application. In this section, you will learn how to create some of the generic classes that you will be able to use and reuse in all of your applications. The following is a list of the generic functions you will learn to create in this chapter.

• Database Connection Class
  
  
• Recordset Class

Database Connection Class

In every database application you design, you will generally need to keep track of several pieces of information to make that connection:

• Data Source Name (DSN)
  
  
• Database Name
  
  
• Login ID
  
  
• Password
  
  
• Are you connected to a database server or a local MDB?
  
  
• Is there a system database for security on an MDB?
  
  
• Database object, connection object, or connection handle
  
  
• Workspace object, engine object, or environment handle

Most programmers will keep this information in separate global variables. Next you would then have some public functions that perform the following services:

• Initialize the database engine, workspace, or environment
  
  
• Open a connection to the database
  
  
• Create a Recordset object from the connection
  
  
• Execute an action query
  
  
• Disconnect from a database
  
  
• Free the resources used by the database engine, workspace, or environment

Each of the above functions relies on one or more of the global variables you defined above. All of this information taken together is an ideal candidate for a class. You have the global variables, which can become properties, and the functions that act upon those global variables (properties), which can become the methods.

Class Name

The name of this database class is DBConnect. To create an instance of this Database Connection Class, you would use the following code:

Public goConnect As New DBConnect

The Database Connection Class you create will be for one type of database access method. Depending on the database access methods you want to use, you will need to create a Database Connection Class for each one. The class you will see in this chapter is the one for Jet.

The Properties

The DBConnect Class will have both private variables and corresponding public properties that are implemented using Property Get and Property Let procedures. Below is a list of each of the public properties that you will use to reference the data from your global database connection object:

The Public Methods

There are some methods that you will need to use to make the connection to the database. Below is a table listing each of the method names and the description of each:

Usage of the Database Connection Class

To use the database connection class, you need to invoke certain methods at certain times. Below is a sample of how you would use the Database Connection Class:

Dim boolPerform As Boolean

boolPerform = goConnect.ConfigGet()

   
' Initialize the Database Engine
If boolPerform Then
   If Not goConnect.OnServer Then

      boolPerform = goConnect.DataInit()

   End If
End If
   
' Open Connection to Database
If boolPerform Then
   If Not goConnect.OnServer Then

      boolPerform = goConnect.DataOpen()

   End If
Else
   Beep
   MsgBox goConnect.InfoMsg
End If

You first call the ConfigGet() method to retrieve all the appropriate information from the registry. You then have to initialize the database engine, and then you can open a connection to the database.

Retrieving Configuration Information- ConfigGet()

The ConfigGet() method will retrieve the base information needed to make a connection to a database. Below is some of the code for this method:

Public Function ConfigGet() As Boolean
   Dim strLoc As String
   Dim vntArray As Variant
   Dim boolPerform As Boolean
   
   On Error GoTo ConfigGet_EH
   

   vntArray = GetAllSettings(App.EXEName, "DataSource")

   If IsEmpty(vntArray) Then

      frmConfig.Show vbModal

      boolPerform = frmConfig.Saved
   Else
      boolPerform = True
   End If
   
   If boolPerform Then
      ' Get Location of Data Source Info

      strLoc = GetSetting(App.EXEName, _

                          "DataSource", _

                          "DataLoc", "")

      
      ' Get the Database Path
      pstrDBPath = GetSetting(App.EXEName, _
                              strLoc, _
                              "DBPath", "")
      
      …
      …
      
      ConfigGet = True
   Else
      ConfigGet = False
   End If
   
   Exit Function
   
ConfigGet_EH:
   ConfigGet = False
   pstrInfoMsg = Err.Description
   plngInfoCode = Err.Number
   Exit Function
End Function

Initializing the Database-DataInit()

You have now seen how to retrieve the majority of the database connection information from the registry. Now you need to use that information to make the connection to the database. DataInit() is the method you will call after calling the ConfigGet() method of the Database Connection Class. The DataInit() method for the Jet engine will initialize the Workspace object. Below is the code for the DataInit() method:

Public Function DataInit() As Integer
   On Error GoTo DataInit_EH
      
   If pws Is Nothing Then

      Set pws = DBEngine.CreateWorkspace("MultiTierApp", _

                                         pstrLoginID, _

                                         pstrPassword, _

                                         dbUseJet)

      
      DataInit = True
   End If
   
   Exit Function
   
DataInit_EH:
   pboolValidLogon = False
   pstrInfoMsg = Err.Description
   DataInit = False
   Exit Function
End Function

Notice how you are creating a generic name for this method instead of something like an OpenWorkspace() method. The reason is you want to create a method name that is generic across many different data access methods. In ADO or in RDO, there is no Workspace object. So an OpenWorkspace() method name would not make sense. By using DataInit(), you can write your Sub Main() to call this routine, and if you change the database connection object to use ADO or RDO, you don’t have to change the Sub Main(), just the DataInit() method.

Opening the Database-DataOpen()

After you have initialized the Workspace object, you then attempt to open the connection to the database by opening a database object. The DataOpen() method is called after DataInit() to establish the connection with the database:

Public Function DataOpen() As Integer
   Dim strConnect As String
   
   On Error GoTo DataOpen_EH
   
   If pdb Is Nothing Then

      DataOpen = True
      If pboolServer Then

         ' Logon to ODBC Data Source

         strConnect = "ODBC" & Me.ConnectString

         Set pdb = pws.OpenDatabase("", False, _

                                    False, strConnect)

      Else

         ' Logon to Local Access Database

         Set pdb = pws.OpenDatabase(pstrDatabase)

      End If

   End If
   DataOpen = True
   pboolValidLogon = True
   
   Exit Function

DataOpen_EH:
   DataOpen = False
   pstrInfoMsg = Err.Description
   pboolValidLogon = False
   Exit Function
End Function

The above method will either open a local database or make a connection to an ODBC data source. By checking the private variable pboolServer, you can determine which OpenDatabase() method to call. After calling the appropriate routine, you now have a Database object and a Workspace object encapsulated within this object.

To return the Database object when using this routine, you will use the DBObject property. Below is the Property Get procedure for the DBObject property:

Property Get DBObject() As Database
   Set DBObject = pdb
End Property

To use this property from anywhere in your source code, you will need to reference the public variable goConnect, then the DBObject property of that object. Below is the code you would use to open a Recordset object:

Dim oRS As Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblDogs"

Set oRS = goConnect.DBObject.OpenRecordset(strSQL, _
                                     dbOpenSnapshot)

Building a Connect String-ConnectString()

In the code for the DataOpen() routine, you will find a call to a method of the Database Connection Class called ConnectString(). This routine will build an ODBC connection string from information in the connection class:

Property Get ConnectString() As String
   Dim strConn As String
   
   strConn = strConn & ";DSN=" & pstrDSN
   strConn = strConn & ";UID=" & pstrLoginID
   strConn = strConn & ";PWD=" & pstrPassword
   strConn = strConn & ";Database=" & pstrDatabase
   strConn = strConn & ";WSID=" & App.EXEName & _
                       "(" & pstrLoginID & ")"
   
   ConnectString = strConn
End Property

To build the connection string, you will need to know which variables your database server requires. The above connection string will work with SQL ServerÔ. Below is an example of what this connection string might look like.

DSN=Dogs;UID=sa;PWD=;Database=Dogs;WSID=MultiApp(sa)

Notice the use of the WSID at the end of the string. This WorkStation ID is passed to SQL Server so that a system administrator can see which connections are being used by which applications and which users. To see this information, execute the sp_who system procedure in SQL Enterprise Manager.

Submitting SQL Statements-Execute()

Many times you may want to submit an SQL statement such as INSERT, UPDATE, or DELETE. These action queries do not return any values, so you can use the Database objects’ Execute() method. In your connection class, you should encapsulate the Database objects Execute() method so that you can duplicate this functionality for other database access methods. Below is the code for the Execute method of the Database Connection Class:

Function Execute(strSQL As String) As Integer
   On Error GoTo Execute_EH

   ' Execute the SQL Statement

   pdb.Execute strSQL

   ' Get number of records affected

   pintRecords = pdb.RecordsAffected


   Execute = True

   Exit Function

Execute_EH:
   Execute = False
   pstrInfoMsg = Err.Description
   plngInfoCode = Err.Number
   Exit Function
End Function

You will pass in an SQL statement to be executed to this method. It will then perform the SQL and store the number of records affected by the query in the RecordsAffected property of the database connection object. Below is an example of how you might call this routine:

…
strSQL = "DELETE FROM tblDogs "
' Submit the SQL Statement

If goConnect.Execute(strSQL) Then

   If goConnect.RecordsAffected = 0 Then

      MsgBox "SQL statement did not change any records"
   End If
End If
…

If the Execute() method is successful, a True value is returned. However, this does not necessarily mean any records were affected by this query. If you want to see if any records were affected, you need to check the RecordsAffected property.

Closing the Database-DataClose()

When you are finished with a database connection, you should close it. This helps free up some client side and server side resources. Ideally you would open a connection, perform some work, then close the connection. You would constantly be opening, closing, and reopening a connection. However, because of performance, you will generally open a connection one time and leave that connection open. The DataClose() method is what you will use to close the database connection:

Public Function DataClose() As Integer
   On Error Resume Next
   
   If Not (pdb Is Nothing) Then
      pdb.Close
      Set pdb = Nothing
   End If
   If Not (pws Is Nothing) Then
      pws.Close
      Set pws = Nothing
   End If
End Function

Recordset Class

Let’s now create another class to interact with databases called a Recordset Class. Some may think this is unnecessary. After all, Jet already has a Recordset object that you can use. While this is true for Jet, not all database access methods have a Recordset object. Some do not even have objects at all. For example, ADO uses an ADODB.Recordset, but RDO uses a Resultset, and the ODBC API simply uses a long integer. Therefore, you need to build a common Recordset Class that you can wrap all of these methods into so that you don’t have to change any other source code in your system; just this one class.

Class Name

The class name for the Recordset Class is DBRecordset. To create an instance of this class, you would use the following code:

Dim oRS As New DBRecordset

Let’s now look at some of the standard private variables in this class that you will generally use in each application. You will also see the public interface for these properties and some of the methods of this class.

The Properties

The DBRecordset Class has both private variables and corresponding public properties that are implemented using Property Get and Property Let procedures. Other variables are used internally by the Recordset Class itself. Below is a list of each of the public properties that you will use to reference the data from your recordset object:

The Public Methods

There are many methods in the DBRecordset Class that you can use to manipulate the Recordset object itself. Below is a table listing each of the method names and the description of each:

Usage of the Recordset Object

To use this DBRecordset object, you will first need to declare the object, then you can use it with syntax very similar to Jet.

Dim oRS As New DBRecordset
Dim strSQL As String
Dim strName As String
Dim lngID As Long

strSQL = "SELECT szDog_nm, lDog_id FROM tblDogs"

oRS.OpenRecordset(strSQL)

Do Until oRS.EOF

   strName = oRS.StringData()

   lngID = oRS.LongData()

   oRS.MoveNext

Loop

oRS.CloseRecordset

You can see that this code looks very similar to Jet. However, you have to retrieve the columns in the exact order you specified them in the SELECT statement. This will allow you to use this DBRecordset across all data access methods. If you want to use a new method for accessing data, such as ADO, you only need to change the DBRecordset object, not all of the source code in your project.

Class Initialization

When you create an instance of the DBRecordset Class, the Class_Initialize() event will automatically set the connection object to the value of the global connection object. This can always be overriden if you have another connection object you want to use. It will also assume you want a Snapshot type of Recordset object created:

Private Sub Class_Initialize()

   Set poConnect = goConnect

   pintRSType = dbOpenSnapshot
   pintOptions = dbForwardOnly
   pintLockType = dbReadOnly
End Sub

Opening a Recordset-OpenRecordset()

To open a set of records against a specific table, you will need to call the OpenRecordset() method just like you would in Jet. You need to pass in an SQL statement and optionally any record set type, options, and lock type you want. Below is the source code for the OpenRecordset() method:

Public Function OpenRecordset(ByVal strSQL As String, _
                      Optional intRSType As Integer = -1, _
                      Optional intOptions As Integer = -1, _
                      Optional intLockType As Integer = -1) _
                               As Boolean
   Dim intStatus As Integer

   On Error GoTo OpenRecordset_EH
   

   ' Reset Column Number

   pintColumn = 0

   
   ' Check Optional Parameters
   If intRSType <> -1 Then
      pintRSType = intRSType
   End If
   If intOptions <> -1 Then
      pintOptions = intOptions
   End If
   If intLockType <> -1 Then
      pintLockType = intLockType
   End If
   
   ' Clear Informational/Error Messages
   Call ClearMessages
   
   ' Open Result Set

   Set poRS = _

       poConnect.DBObject.OpenRecordset(strSQL, _

                                        pintRSType, _

                                        pintOptions, _

                                        pintLockType)


   If poRS.EOF Then
      Call CloseRecordset
      OpenRecordset = False
   Else
      OpenRecordset = True
      pboolRSActive = True
   End If
   
   Exit Function

OpenRecordset_EH:
   pintInfoCode = Err.Number
   pstrInfoMsg = Err.Description
   OpenRecordset = False
   Exit Function
End Function

Notice how the pintColumn variable is set to 1. This allows you to just call one of the *Data() methods to retrieve the appropriate data type for the 1^st column.

Retrieving Column Data-*Data() methods

There are many methods that return column data. You may return each column as a specific data type, or you may simply choose to return all columns as a variant. Below are two of the *Data() methods:

Public Function VarData(Optional intColumn As Variant) _
                        As Variant
   
   On Error GoTo Var_EH
   

   If Not IsMissing(intColumn) Then

      pintColumn = intColumn

   End If

   
   ' Retrieve Column Data

   VarData = poRS(pintColumn)

   
   ' Increment The Column Number
   pintColumn = pintColumn + 1
   
   Exit Function

Var_EH:
   pintInfoCode = Err.Number
   pstrInfoMsg = Err.Description
   VarData = False
   Exit Function
End Function

Here is the function to return LongData():

Public Function LongData(Optional intColumn As Variant) _
                      As Long
   
   On Error GoTo Long_EH
   

   If Not IsMissing(intColumn) Then

      pintColumn = intColumn

   End If

   
   ' Retrieve The Data

   LongData = Field2Long(poRS(pintColumn))

   
   ' Increment the Column Number
   pintColumn = pintColumn + 1
   
   Exit Function

Long_EH:
   pintInfoCode = Err.Number
   pstrInfoMsg = Err.Description
   LongData = False
   Exit Function
End Function

Moving the Record Pointer-Move*()

There are four methods that you can use to move the record pointer in various directions. MoveNext(), MovePrevious(), MoveFirst(), and MoveLast() are the method names. These are pretty self-explanatory, so let’s just look at a couple:

Public Function MoveNext() As Boolean
   Dim intStatus As Integer
   
   MoveNext = False
   
   On Error GoTo MoveNext_EH

   If pboolRSActive Then
      ' Reset Column Number
      pintColumn = 0
      ' Move To Next Row

      poRS.MoveNext

      If Not poRS.EOF Then

         pboolEOF = True
         MoveNext = True
      Else
         pboolEOF = False
         MoveNext = False
      End If
   End If
   
   Exit Function

MoveNext_EH:
   pintInfoCode = Err.Number
   pstrInfoMsg = Err.Description
   pboolEOF = False
   MoveNext = False
   Exit Function
End Function

Here is the MoveFirst() method:

Public Function MoveFirst() As Boolean
   On Error GoTo MoveFirst_EH
   
   If pboolRSActive Then

      poRS.MoveFirst

      MoveFirst = True
   End If
   
   Exit Function
   
MoveFirst_EH:
   pintInfoCode = Err.Number
   pstrInfoMsg = Err.Description
   MoveFirst = False
   Exit Function
End Function

Closing a Recordset-CloseRecordset()

When you are through with a Recordset object, you should always close that record set. Unfortunately, in the Microsoft® Visual Basic® programming system, Close, is a reserved word, and you may not use reserved words for method names. Thus, you will need to name this method CloseRecordset():

Public Sub CloseRecordset()
   Dim intStatus As Integer
   
   On Error Resume Next
   
   If pboolRSActive Then
      ' Release Result Set

      poRS.Close

      Set poRS = Nothing

      pboolRSActive = False
   End If
   
   ' Clear Information/Error Messages
   Call ClearMessages
End Sub

Data Access Template

Below is a table of the services our data access classes should accomplish for each of the base tables contained in our database application.

Besides the standard methods that perform actions, you will also have several properties that hold information about the object. These include a reference to a connection object and an instance of the DBRecordset object. A filter name for adding a WHERE clause. A filter name for adding an ORDER BY. A filter name for the SELECT statement to use:

' Private Connection and Recordset
Private poConnect As DBConnect
Private poRS As New DBRecordset

' Filters for WHERE, ORDER BY and SELECT
Private pstrFilterName As String
Private pstrSortName As String
Private pstrSelectFilter As String

' For Info & Error Handling Messages
Private pstrInfoMsg As String
Private pintInfoCode As Integer

All of the rest of the properties in your data access classes will be unique based on the underlying base table the class is based on. In this section, you will look at a class that deals with a dog information table.

Dog Class

There is a table in our dogs database called tblDogs. This table holds various information about the dog in the application. Below is the structure for this table:

Properties

In our Data Access Class, you will need private variables, and Property Get and Property Let procedures that correspond to each of the column names in the above table. You should use hungarian notation for the private variables, but make meaningful names for the properties. Below is a table showing each of the columns, public names, and private names:

OpenRecordset Method

The first standard method you will create for your Data Access Class is one that will create a result set of data from our underlying base table. This method will use other properties and methods of the class to perform its job:

Public Function OpenRecordset() As Boolean
   Dim strSQL As String

   Screen.MousePointer = vbHourglass

   ' Initialize Variables
   OpenRecordset = False
   pstrInfoMsg = ""
   
   ' Set Connection

   Set poRS.DBObject = poConnect


   ' Build Select Statement

   strSQL = SelectSQL()


   ' Build Recordset Object

   If poRS.OpenRecordset(strSQL) Then

      ' Retrieve the info

      Call Retrieve

      OpenRecordset = True
   Else

      Call CloseRecordset

      OpenRecordset = False
      ' Get Any Error Information

      pstrInfoMsg = poRS.InfoMsg

      pstrInfoCode = poRS.InfoCode

   End If
   
   Screen.MousePointer = vbDefault
End Function

This method first needs to take the connection object from itself and give it to its DBRecordset object. This will ensure that both this object and the Recordset object are using the same connection. This is only needed if you will be making multiple connections to one or to many different database sources.

Next, this method will need to create the SELECT statement to create the set of records. You will learn how to build this SELECT statement later.

After you have the SELECT statement, you can now pass this SELECT statement to the OpenRecordset() method of the DBRecordset object you created. This routine will use the current data access method to create a result set. If the result set is created correctly, a True value will be returned. If it is not created correctly, a False value will be returned. You can then get any error messages and numbers by checking the InfoMsg and InfoCode properties of the DBRecordset object.

Notice that the OpenRecordset method uses a poConnect object. This is set in the Class_Initialize() for this class to the value of goConnect. There is a Connection property that you may use to override this connection with another connection.

Private Sub Class_Initialize()
   Set poConnect = goConnect
End Sub

SelectSQL Method

The SelectSQL is a private method that will return a SELECT statement based on the SelectFilter property. You can create as many SELECT CASE statements as you want for however many SELECT statements you want to use. Below is the source code for the SelectSQL method:

Private Function SelectSQL() As String
   Dim strSQL As String

   Select Case UCase$(pstrSelectFilter)

      Case "LISTBOX"

         strSQL = "SELECT "
         strSQL = strSQL & "lDog_id, "
         strSQL = strSQL & "szDog_nm "
         strSQL = strSQL & "FROM tblDogs "
      

      Case "LISTBREEDLOAD"

         strSQL = "SELECT lDog_id, "
         strSQL = strSQL & "szDog_nm, "
         strSQL = strSQL & "szBreed_nm  "
         strSQL = strSQL & "FROM tblDogs, tblBreed "
         strSQL = strSQL & "WHERE tblDogs.lBreed_id = tblBreed.lBreed_id "

      Case Else

         strSQL = "SELECT "
         strSQL = strSQL & "lDog_id, "
         strSQL = strSQL & "szDog_nm, "
         strSQL = strSQL & "lBreed_id, "
         strSQL = strSQL & "lBreeder_id, "
         strSQL = strSQL & "szColor_nm, "
         strSQL = strSQL & "sBark_type, "
         strSQL = strSQL & "cCost_amt, "
         strSQL = strSQL & "cPrice_amt, "
         strSQL = strSQL & "dtBirth_dt, "
         strSQL = strSQL & "sSex_nm, "
         strSQL = strSQL & "sLastUpdate_id, "
         strSQL = strSQL & "dtLastUpdate_dt, "
         strSQL = strSQL & "iConcurrency_id "
         strSQL = strSQL & "FROM tblDogs "
   End Select

   ' Add Filter To This SELECT Statement

   strSQL = strSQL & FilterGet()


   ' Add Sort To This SELECT Statement

   strSQL = strSQL & SortGet()


   SelectSQL = strSQL
End Function

After checking the pstrSelectFilter variable (SelectFilter property), a SELECT statement is built. Next the FilterGet() method is called to add any WHERE clause that may have been requested. Finally the SortGet() method is called to add any ORDER BY clause that may have been requested.

FilterGet() Method

The FilterGet() method is another method that can change over time as you decide you need to retrieve your data using different critieria. Below is the code for the FilterGet() method:

Private Function FilterGet() As String
   Dim strSQL As String

   ' Select Records using Requested Filter

   Select Case UCase$(pstrFilterName)

      Case "PRIMARYKEY"

         strSQL = strSQL & " WHERE lDog_id = " & plngDogId
      Case "DOGNAME"
         strSQL = strSQL & " WHERE szDog_nm = " & Str2Field(pstrDogName)
      Case "BREEDID"
         strSQL = strSQL & " WHERE lBreed_id = " & plngBreedId
      Case "BREEDID_BREED"
         strSQL = strSQL & " AND tblDogs.lBreed_id = " & plngBreedId
      Case "BREEDERID"
         strSQL = strSQL & " WHERE lBreeder_id = " & plngBreederId
      Case ""
         ' Do Nothing
      Case Else
         MsgBox "clsDogs Can't Find Filter Passed: " & pstrFilterName
   End Select

   FilterGet = strSQL
End Function

You may use this method for WHERE clauses for SELECTs or DELETEs. You can create any WHERE clause you want within each CASE statement.

SortGet() method

The SortGet() method is a method you can modify as you decide you need additional ordering of your result sets. Below is the code for the SortGet() method:

Private Function SortGet() As String
   Dim strSQL As String

   ' Add ORDER BY

   Select Case UCase$(pstrSortName)

      Case "DOGNAME"

         strSQL = strSQL & " ORDER BY szDog_nm"
   End Select

   SortGet = strSQL
End Function

MoveNext() Method

The MoveNext() method is used to move to the next row in the result set, then call the Retrieve() method to load the current row of the result set into the private variables in the object. This method returns a False when the end of the file is hit:

Public Function MoveNext() As Boolean
   ' Move to Next Record

   If poRS.MoveNext() Then

      ' Load Properties

      Call Retrieve

      MoveNext = True
   Else
      MoveNext = False
      Call CloseRecordset
      pstrInfoMsg = poRS.InfoMsg
   End If
End Function

The first call is to the DBRecordset object to have it move to the next row in the result set. If this method returns a True value, then the Retrieve() method is called to take the data from the result set and move it into the private variables.

Retrieve() Method

The retrieve method will use the SelectFilter filter in conjunction with the SelectSQL() method. The number of columns you are selecting must match the number of columns you are retrieving. If you change your SELECT statement, you must remember to change your retrieval method also:

Private Sub Retrieve()
   With poRS

      Select Case UCase(pstrSelectFilter)

         Case "LISTBOX"

            plngDogId = .LongData()
            pstrDogName = .StringData()
         

         Case "LISTBREEDLOAD"

            plngDogId = .LongData()
            pstrDogName = .StringData()
            pstrBreedName = .StringData()
   

         Case Else

            plngDogId = .LongData()
            pstrDogName = .StringData()
            plngBreedId = .LongData()
            plngBreederId = .LongData()
            pstrColorName = .StringData()
            pstrBarkType = .StringData()
            pcurCostAmount = .CurrencyData()
            pcurPriceAmount = .CurrencyData()
            pstrBirthDate = .StringData()
            pstrSex = .StringData()
            pstrLastUpdateId = .StringData()
            pstrLastUpdateDate = .StringData()
            pintConcurrencyId = .ConcurrencyData()
      End Select
   End With
End Sub

Notice that this method retrieves the data from the DBRecordset object. It retrieves each column in the order specified in the SELECT and uses the appropriate method of the DBRecordset object to retrieve the data type. Be sure to always retrieve the columns in the exact order you specified them in the SELECT statement. Doing this ensures that you can choose any data access method you want. The reason for this is the ODBC API must retrieve columns in the exact order in which you specified them in the SELECT statement.

Find() Method

This method will create a result set based on the primary key filter. You must be sure to fill in the primary key property of your object prior to calling the Find() method. Find simply sets the SelectFilter property to “PRIMARYKEY” prior to calling the FindByFilter() method:

Public Function Find() As Boolean
   Dim strOldFilter As String

   ' Set Filter For Retrieving One Row

   strOldFilter = pstrFilterName

   pstrFilterName = "PRIMARYKEY"


   Find = FindByFilter()

   
   ' Reset The Filter
   pstrFilterName = strOldFilter
End Function

Note   Find will always return only one row.

FindByFilter() Method

This method is called whenever you want to retrieve a result set based on a FilterName and some property or properties. The idea is if you want to find all dogs by a certain breed, you would fill in the BreedID property, then fill in the FilterName property with a string that defines a WHERE clause in the FilterGet() method:

Public Function FindByFilter() As Boolean
   ' Build Recordset & Retrieve Data

   If OpenRecordset() Then

      FindByFilter = True
   Else
      Call CloseRecordset
      FindByFilter = False
   End If
End Function

Of course you could always just call the OpenRecordset() method.

Check() Method

Business rules must be enforced for all of your data. For example, in our Dog Class you should create a rule that no dog record can be saved unless there is a Dog’s Name filled in, and if a birth data is filled in, that it is a valid date. All of these business rules should go into a method called Check(). Below is an example of a Check() method for the Dog Class:

Public Function Check() As Boolean
   Check = False

   If Trim$(pstrDogName) = "" Then

      pstrInfoMsg = "Dog Name must be filled in."

   ElseIf Trim$(pstrBirthDate) <> "" Then

      If Not IsDate(pstrBirthDate) Then

         pstrInfoMsg = "Birth Date is an invalid date"
      Else
         Check = True
      End If
   Else
      Check = True
   End If
End Function

The Check method is called from both the AddNew() and Replace() methods in our Dog Class. This ensures that the rules are always checked prior to saving the data to the base table. Notice that no message boxes are displayed from this routine. It simply returns a True or False value. If anything is in error, the InfoMsg property is filled in with an error message that the user can print out using their client software.

AddNew() Method

The AddNew method is responsible for inserting a new row into the base table. You will need to decide if you will be using an Identity or AutoNumber type of field for the primary key or not. If you are, then you do not need to include the primary key in the INSERT. If you are not, then you will need to write a routine to generate a unique ID for each table.

Public Function AddNew() As Boolean
   Dim strSQL As String

   AddNew = False

   Screen.MousePointer = vbHourglass

   ' Check Business Rules and Duplicate Values First

   If Check() Then

      strSQL = "INSERT INTO tblDogs ("

      strSQL = strSQL & "szDog_nm, "
      strSQL = strSQL & "lBreed_id, "
      strSQL = strSQL & "lBreeder_id, "
      strSQL = strSQL & "szColor_nm, "
      strSQL = strSQL & "sBark_type, "
      strSQL = strSQL & "cCost_amt, "
      strSQL = strSQL & "cPrice_amt, "
      strSQL = strSQL & "dtBirth_dt, "
      strSQL = strSQL & "sSex_nm, "
      strSQL = strSQL & "sLastUpdate_id, "
      strSQL = strSQL & "dtLastUpdate_dt, "
      strSQL = strSQL & "iConcurrency_id "
      strSQL = strSQL & ") VALUES ("
      strSQL = strSQL & Str2Field(pstrDogName) & ", "
      strSQL = strSQL & plngBreedId & ", "
      strSQL = strSQL & plngBreederId & ", "
      strSQL = strSQL & Str2Field(pstrColorName) & ", "
      strSQL = strSQL & Str2Field(pstrBarkType) & ", "
      strSQL = strSQL & pcurCostAmount & ", "
      strSQL = strSQL & pcurPriceAmount & ", "
      strSQL = strSQL & Date2Field(pstrBirthDate) & ", "
      strSQL = strSQL & Str2Field(pstrSex) & ", "
      strSQL = strSQL & Str2Field(poConnect.LoginID) & ", "
      strSQL = strSQL & Date2Field(Date) & ", 1) "

      ' Submit the SQL Statement

      If poConnect.Execute(strSQL) Then

         If poConnect.RecordsAffected = 1 Then

            AddNew = True
         Else
            AddNew = False
         End If
      Else

         pstrInfoMsg = poConnect.InfoMsg

         AddNew = False
      End If
   End If

   Screen.MousePointer = vbDefault
End Function

The INSERT statement is submitted to the database using the connection object’s Execute() method. After this routine returns a True value, you can check the RecordsAffected property to determine if one row was added. If the Execute() method fails, you can retrieve any error information from the connection object.

Replace Method

The Replace method is very similar to the AddNew() method. It will build an UPDATE statement using the data in the properties of the class, then submit that statement to the database for processing:

Public Function Replace() As Boolean
   Dim strSQL As String

   Screen.MousePointer = vbHourglass

   If Check() Then

      strSQL = "UPDATE tblDogs SET "

      strSQL = strSQL & "szDog_nm = " & Str2Field(pstrDogName) & ", "
      strSQL = strSQL & "lBreed_id = " & plngBreedId & ", "
      strSQL = strSQL & "lBreeder_id = " & plngBreederId & ", "
      strSQL = strSQL & "szColor_nm = " & Str2Field(pstrColorName) & ", "
      strSQL = strSQL & "sBark_type = " & Str2Field(pstrBarkType) & ", "
      strSQL = strSQL & "cCost_amt = " & pcurCostAmount & ", "
      strSQL = strSQL & "cPrice_amt = " & pcurPriceAmount & ", "
      strSQL = strSQL & "dtBirth_dt = " & Date2Field(pstrBirthDate) & ", "
      strSQL = strSQL & "sSex_nm = " & Str2Field(pstrSex) & ", "
      strSQL = strSQL & "sLastUpdate_id = " & _
                          Str2Field(poConnect.LoginID) & ", "
      strSQL = strSQL & "dtLastUpdate_dt = " & Date2Field(Date) & ", "
      If pintConcurrencyId = -1 Then
         strSQL = strSQL & "iConcurrency_id = 1 "
      Else
         strSQL = strSQL & "iConcurrency_id = iConcurrency_id + 1 "
      End If
      strSQL = strSQL & " WHERE "
      strSQL = strSQL & "lDog_id = " & plngDogId & " "
      If pintConcurrencyId = -1 Then
         strSQL = strSQL & " AND iConcurrency_id = Null"
      Else
         strSQL = strSQL & " AND iConcurrency_id = " & pintConcurrencyId
      End If

      ' Submit the SQL Statement

      If poConnect.Execute(strSQL) Then

         If poConnect.RecordsAffected = 1 Then

            Replace = True
            If pintConcurrencyId = -1 Then
               pintConcurrencyId = 1
            Else
               pintConcurrencyId = pintConcurrencyId + 1
            End If
         Else
            Replace = False
            pstrInfoMsg = poConnect.InfoMsg & _
                          vbCrLf & "Could Not Update This Table: tblDogs "
         End If
      Else
         Replace = False
      End If
   End If

   Screen.MousePointer = vbDefault
End Function

The UPDATE statement is submitted using the Execute() method of the connection object. You then check the RecordsAffected property to see if one row was updated. If it was, you know that the update was successful. If not, then you know it was not, and you can return error information.

Multiple User Considerations

When performing updates on data, you must be careful not to overwrite other people’s changes. Take the example where two people read the same record at the same time. If User 1 replaces the information first, User 2 still has the old data in their memory. If User 2 updates that information, the changes from User 1 will be lost.

To stop this from happening, you must have some mechanism to determine if the data has changed prior to performing an update. There are several techniques to make this happen. First you could reread the data from the table and compare field by field to an old copy of the original data. If any one field has changed, you know someone else has updated the row.

Another method is to use a TimeStamp. This is a special type of field that is updated by the database server everytime, and UPDATE is performed on a row. Unfortunately, not all database servers support this, and ISAM file systems such as Microsoft Access or dBase do not have this capability at all.

Knowing this, you need to devise some method of keeping track of when something changes.

A method that has worked very succssfully at our shop is to add one column to every table called iConcurrency_id. This is an Integer type that starts at the value 1 and will be incremented every time you perform an UPDATE. Unfortunately, you must take care of updating this value yourself. Look at the Replace() method you wrote earlier. In the UPDATE statement, you wrote the following:

…

If pintConcurrencyId = -1 Then

   strSQL = strSQL & "iConcurrency_id = 1 "
Else
   strSQL = strSQL & "iConcurrency_id = iConcurrency_id + 1 "
End If
strSQL = strSQL & " WHERE "
strSQL = strSQL & "lDog_id = " & plngDogId & " "
If pintConcurrencyId = -1 Then
   strSQL = strSQL & " AND iConcurrency_id = Null"
Else
   strSQL = strSQL & " AND iConcurrency_id = " & pintConcurrencyId

End If

First you check to see if the concurrency id is equal to –1. This means that it was a Null. If the value is Null, you want to set the concurrency id to 1 Otherwise, you increment the concurrency id by one. Now take the scenario presented above. If User 1 and User 2 both read the same row at the same time, they both read the same value for the concurrency id.

Say the value was a 1, then User 1 updates the row. The concurrency id is now equal to 2. User 2 still has the value of a 1 in their memory. When they attempt the update, the WHERE clause says to to check for where the concurrency id is equal to the value in User 2’s private variable pintConcurrencyId. This value is a 1. This means that the UPDATE statement will not find any rows, so the update will not take place. User 2 will receive 0 rows in the RecordsAffected property, which will signal that another user has updated the data before them. User 2 should then refresh their data to get User 1’s updated data.

Delete() Method

The Delete() method will delete a row from the base table based on the primary key. Only one row will be deleted from the table. You need to fill in the primary key property with the value of the row you want to delete:

Public Function Delete() As Boolean
   Dim strOldFilter As String

   Screen.MousePointer = vbHourglass

   ' Set Filter Name
   strOldFilter = pstrFilterName

   pstrFilterName = "PRIMARYKEY"


   Delete = DeleteByFilter()

   If poConnect.RecordsAffected = 1 Then

      Delete = True
   Else
      Delete = False
   End If

   ' Reset The Filter
   pstrFilterName = strOldFilter

   Screen.MousePointer = vbDefault
End Function

You can see that this method simply sets a filter to “PRIMARYKEY,” then calls the DeleteByFilter() method. It then checks the connection object’s RecordsAffected property to see if a 1 is returned. If a 1 is returned, then you know the row was deleted.

DeleteByFilter() Method

This method is called whenever you want to delete a set of rows based on a FilterName and some property or properties. The idea is if you want to delete all dogs by a certain breed, you would fill in the BreedID property, then fill in the FilterName property with a string that defines a WHERE clause in the FilterGet() method:

Public Function DeleteByFilter() As Boolean
   Dim strSQL As String

   Screen.MousePointer = vbHourglass

   ' Setup Delete SQL

   strSQL = "DELETE FROM tblDogs "


   ' Get The WHERE Clause for the DELETE statement
   ' Using the Existing Filter

   strSQL = strSQL & FilterGet()


   ' Submit the SQL Statement

   If poConnect.Execute(strSQL) Then

      DeleteByFilter = True
   Else
      pstrInfoMsg = poConnect.InfoMsg
      DeleteByFilter = False
   End If
   
   Screen.MousePointer = vbDefault
End Function

CloseRecordset Method

When you are finished with the object, you should always close the recordset that was generated. The CloseRecordset method is used to close the recordset:

Public Sub CloseRecordset()
   If pboolRSActive Then
      poRS.Close
      pboolRSActive = False
   End If
End Sub

In fact, this method is called from the Class_Terminate() event. This handles the case where the object goes out of scope and the CloseRecordset method was not called:

Private Sub Class_Terminate()
   Call CloseRecordset
End Sub

Changing the Database Access Method

This next section will present Database Connection Classes and Recordset Classes for different data access methods. The idea is to build a set of classes using different data access methods that you can plug into your application. This means you can swap your data access method at any time and not have to rewrite your application.

Database Connection Class-ODBCDirect

You are already familiar with the Database Connection Class for Jet. The connection class for ODBCDirect is very similar to this. Let’s look at where the changes have been made in this class so that you can get an idea of how to create additional Database Connection Classes as you need them.

Class Name & Public Interface

The name of this database class is STILL DBConnect. You never want to change the name of the Class or any of the public interface. You can modify the private interface all you want as long as the functionality remains the same.

The Private Interface

There is just one change to make to the private variables for the ODBCDirect Connection Class. Instead of a Database object, you need to use a Connection object:

Private poConn As Connection

The Public Properties

There is only one public property that needs to change to implement ODBCDirect. First is the Property Get procedure for the DBObject. This property returns a reference to the connection object. In the case of Jet, it was a Database object. For ODBCDirect, it is a Connection object. Below is the code for the DBObject() property:

Property Get DBObject() As Connection

   Set DBObject = poConn

End Property

The Public Methods

The major changes for converting to a new database access method will occur in the public methods you created for this class. Specifically, the DataInit and DataOpen methods will need to be rewritten for ODBCDirect. Below are the samples for each one of these methods.

Initializing the Database- DataInit()

The DataInit() method is similar to the Jet engine version except the last parameter passed to the CreateWorkspace() method is to use ODBCDirect instead of Jet.

Public Function DataInit() As Integer
   On Error GoTo DataInit_EH
   
   If pws Is Nothing Then

      Set pws = _

          DBEngine.CreateWorkspace("MultiTierODBCDirect", _

                                   "sa", "", _

                                   dbUseODBC)

   End If
   
   DataInit = True
   
   Exit Function
   
DataInit_EH:
   pboolValidLogon = False
   pstrInfoMsg = Err.Description
   plngInfoCode = Err.Number
   DataInit = False
   Exit Function
End Function

Opening the Database-DataOpen()

In the DataOpen() method, you will need to open a Connection object instead of a Database object as in Jet:

Public Function DataOpen() As Integer
Dim strOldMsg As String
   
   On Error GoTo DataOpen_EH
   
   ' Display Status Message
   strOldMsg = StatusProcess("Opening Database...")
   
   DataOpen = True
   

   ' Open the Connection Object

   strConnect = "ODBC" & Me.ConnectString

   Set poConn = _

       pws.OpenConnection("", _

                          dbDriverNoPrompt, _

                          False, _

                          strConnect)


   pboolOpen = True
   
   Call StatusRestore(strOldMsg)
   
   DataOpen = True
   pboolValidLogon = True
   
   Exit Function

DataOpen_EH:
   DataOpen = False
   pstrInfoMsg = Err.Description
   plngInfoCode = Err.Number
   pboolValidLogon = False
   Exit Function
End Function

Recordset Class-ODBCDirect

The Recordset Class for ODBCDirect is exactly the same as the Jet engine. The only difference is in the types of Recordset objects you can create. You are a little more limited on the range of Recordset objects you can create.

Creating Code Components

Have you ever wanted an easy way to use just a portion of your application from a Microsoft Office application? Or maybe you needed to use some of it from a Microsoft C++ application. By creating an ActiveXÔ code component (formerly OLE Server) as a component, you are able to expose objects from your server to another application. The best part is you can use this server as just a normal application also.

These code components are a core part of creating a multi-tier architecture. Learning to create code components that are exposed in a server is actually quite simple. In this chapter, you will learn how to create servers that can be called from many different applications.

ActiveX Code Components

Probably the most exciting technology added to Visual Basic is the ability to create ActiveX code components. By building class modules and exposing properties and methods, you can create an application that has the ability to be called from any client application. You can create both in-process (DLLs) and out-of-process (EXE) code components. Using this technology, you can easily build a multi-tier client/server architecture.

Another feature of the code component is the ability to run the code component on any workstation in your network. This means you can put your business rules and data access routines on one very powerful machine, and all of your users can simply use the CPU of this other machine to perform the validation and data manipulation routines.

Creating a Simple Code Component

Let’s now walk step by step through the creation of a simple code component. You will create a Dog object that has two properties; Bark and Name. You will then create one method for this Dog object called BarkTheDog. This method will simply return a string that tells the dog’s name and what type of bark the dog has.

Sample File Name: AutoDog.vbp

1. Start Visual Basic version 5.0.
   
   
2. Select ActiveX EXE from the list of project templates.
   
   
3. Press F4 to bring up the Properties window.
   
   
4. Set the Name Property to Dog.
   
   
5. Set the Instancing Property to 6 - GlobalMultiUse.
   
   
6. Add the following private variables to the (General) (Declarations) area of this Class:

   Option Explicit
   
   Private pstrName As String
   Private pstrBark As String
   

Read Properties

After creating the private variables, you now need to create a couple of Property Get procedures so that you can read the data in each of these private variables:

Property Get Name() As String
   Name = pstrName
End Property

Property Get Bark() As String
   Bark = pstrBark
End Property

Class Initialization

To keep things simple, you should just default the private variables to some values. Enter the following code in the Class_Initialize event:

Private Sub Class_Initialize()
   pstrName = "Rusty"
   pstrBark = "Howl"
End Sub

When the object is instantiated, the above code will fill in the values for this object.

Bark the Dog Method

Let’s create a method that will return the Dog’s name and bark. You will create a string that can be displayed in any front end:

Public Function BarkTheDog() As String
   Dim strMsg As String
   

   strMsg = "My Name is " & pstrName & vbCrLf

   strMsg = strMsg & " and I can " & pstrBark

   

   BarkTheDog = strMsg

End Function

Creating the Server

Now that all the pieces are filled in, you should fill in some project attributes so that you can identify this server by a meaningful name and a description:

1. Select Project, Project1 Properties… from the menu.
   
   
2. Set the Project Name to AutoDog. This will become the name of your code component.
   
   
3. Set the Project Description to “Automation Server For Dog Object.” This will be the name that will show up in the References section of any client application that needs to use this class.

Your screen should look something like the following:

When you are finished, click the OK button to save the changes. At this point you should save your project.

Run the Server

Now run this project. The application will appear to start and will be in run mode, but will not do anything.

Testing the Server

To test this code component, follow the steps below:

1. Open another instance of Visual Basic.
   
   
2. Select the Standard EXE option from the list of project templates.
   
   
3. Add a Command button to the form in the project.
   
   
4. Double-click on the Command button to bring up the code window.

   Private Sub Command1_Click()
       Dim oDog As New Dog
       
       MsgBox oDog.BarkTheDog
   End Sub
   

Of course, this instance of Visual Basic does not know what a Dog object is, so you need to tell it where to find the server that has this type of object. Follow the steps below to specify the reference to the server:

1. Select Project, References from the menu.
   
   
2. Find the Available Reference named “Automation Server For Dog Object” and select it. This is our code component that is running in the other instance of Visual Basic.
   
   
3. Press the OK button.
   
   
4. Run the project and click on the Command button. If everything went OK, you should see the following Message Box:

   

Congratulations! You have created your first code component in Visual Basic.

Running from Microsoft Excel

Let’s now test our code component from Microsoft Excel. To accomplish this, follow the steps below:

1. Start your code component in Visual Basic Design Mode.
   
   
2. Start up Microsoft Excel version 5.
   
   
3. Type in the code below.

   Sub Test()
      Dim oDog As New Dog
      
      MsgBox oDog.BarkTheDog
   End Sub
   

There are actually two methods for calling a code component. The above method uses a technique called early binding. The code below will use a technique called late binding:

Sub Test()

   Dim oDog As Object

   
   Set oDog = CreateObject("AutoDog.Dog")
   
   MsgBox oDog.BarkTheDog
End Sub

Notice how you just declared the oDog variable to be of the generic type Object. You then must use the CreateObject() function and pass as a paramter the name of the server and the name of the Class inside that server from which you want to create an object.

Binding

In the examples used in the previous section, we used two different kinds of binding techniques; early and late. Early binding means you have explicitly declared a code component name and a specific object in that code component in a Dim statement. For example:

     Dim oDog As New AutoDog.Dog

Since you have explicitly stated the name of the server and the object within that server you are using early binding.

With late binding, you are waiting until run time to determine the object that will be loaded, and what server will be used. To use late binding, you must use the CreateObject() method.

Dim oDog As Object
    
Set oDog = CreateObject("AutoDog.Dog")

The CreateObject() method will access the system registry at run time to find out which code component to run.

Reasons for Binding

There are different reasons why you may want to use late binding versus early binding or vice versa. Early binding is much faster, but this also means that if you change your code component and you change something in this version, it could cause your client application to fail.

Late binding is slower, but if you change the version of your code component, your application may still continue to run, as long as the properties and methods are still compatible.

Registering and Unregistering

To register a code component after making an .EXE file, locate the file in File Manager, and double-click on the .EXE file. Visual Basic adds start-up code to any .EXE you make that has objects in it with the Public property set to true. This code tells Visual Basic to create registry entries for all exposed objects. Double-clicking on the code component does not start the code component.

Code components are only started at the request of another application and unloaded when all references to them are destroyed. Entries for your code components are contained under the HKEY_CLASSES_ROOT hive. The registered objects will all have a prefix of the Project Name you entered under the Project, Properties.., General tab.

To unregister a code component from the registry, run the .EXE file with the /U command line option.

Data Access Code Component

Active code components may be used in two ways: either through Automation or by embedding an object in a Visual Basic form and using Visual Basic as the Active Container. Using Visual Basic, you can create code components that can be controlled through Automation.

So the question becomes, what kind of code component would you build using Visual Basic that can be useful to other programs? One of the best examples is a multi-tier client/server application. This type of architecture is based on having your user interface as one .EXE, all of the business rules and data access routines as a separate .EXE or .DLL (a code component), and the database engine as the third level. Other uses of code components are specialized applications like Microsoft Excel, or a drawing package like Visio.

Master   Note: You must have Visual Basic 5.0 Professional Edition or Enterprise Edition to run this sample.

BARK server

Let’s now take the BARK application that you created earlier and separate the data access classes from the user interface. The idea is you will create two separate project files that each have different pieces of the complete BARK application in it. One project will have the CLS files and some .BAS files (the server), while the other will have the FRM files and some .BAS files, but no CLS files (the client).

Once you have created these two projects, you need to create an EXE out of the Server, then close that instance of Visual Basic. Next you load the client and, using the early binding technique shown earlier, set a reference to the Dog server.

Sample project files: DogServ.vbp and DogClient.vbp

Steps to run the code component:

1. Open the project dogserv.vbp and run the project.
   
   
2. Open the project dogclient.vbp.
   
   
3. Set a reference to the “Server for BARK Application” in the Project, References…
   
   
4. Run the project.
   
   
5. You should now have the Dogs application running against the code component.

The reason the objects from the data access classes are available is because of the reference that was set to the “Server for BARK Application.” If you stop the other instance of Visual Basic from running, then return to this instance, you should not see this reference anymore. What happens is that Visual Basic temporarily registers the code component so that other applications can see it. When you end the program, Visual Basic cleans up the registry.

Using the code component from Microsoft Excel

Now that you have made a real code component out of the BARK application, you can use this code component from any application that supports Automation. Let’s call our Dog object from Microsoft Excel and create a list of dogs in the spreadsheet.

1. To begin, you can open bark.xls in Microsoft Excel.
   
   
2. Click on the Command button to load the list of dogs and their birth dates.

   

Here is the VBA code you will write in Microsoft Excel:

Private Sub cmdLoad_Click()

   Dim oApp As New DogServer.Application

   Dim oDog As New DogServer.Dog
   Dim boolPerform As Boolean
   Dim intRow As Integer
   
   Call oApp.OpenDatabase

   Range("A4:B100").Select
   Selection.ClearContents
   Range("A4").Select

   oDog.SortName = "DOGNAME"
   boolPerform = oDog.OpenRecordset
   If boolPerform Then
      intRow = 4
      Do While boolPerform
         Worksheets("DogList").Cells(intRow, 1) = oDog.DogName
         Worksheets("DogList").Cells(intRow, 2) = _
                                    oDog.BirthDate
         intRow = intRow + 1
         
         boolPerform = oDog.MoveNext
      Loop
      oDog.CloseRecordset
   Else
      Beep
      MsgBox oDog.InfoMsg
   End If
End Sub

The only difference between the code shown above and the code from the Visual Basic sample is that you specify the name of the server as well as the object within the object. Also, you specify the cells you want to put the data into. Other than that, the code is nearly identical to the code that you write in Visual Basic.

Out-of-Process Servers

When you make an .EXE file out of a Visual Basic project that has a public class, you are creating an out-of-process server. This means that your application .EXE will run in one process space, and the code component you call will run in another process space. You will be performing cross-process communication via Active Automation.

In-Process servers

An In-Process Server (IPS) is an Active DLL that can be created in Visual Basic. These Active DLLs have to be created with the 32-bit version of Visual Basic and can only be run in a 32-bit environment. These DLLs share the same process space as the .EXE file that uses them. This makes them much faster than the out-of-process code components. Using an in-process server is exactly the same as an out-of-process code component.

Rules of In-Process servers

These require particular coding conventions, and there are a lot of rules to programming them. You should learn some of the these rules before coding your servers. Below are some of the rules for an IPS:

• No MDI forms are allowed.
  
  
• A Sub Main is required and will load every time the code component is loaded.
  
  
• The End Statement is not allowed.
  
  
• You may not set the Instancing property to Createable SingleUse since this requires another instance of the server to load, and DLLs can only be loaded once.
  
  
• DDE can not be used from the IPS.

This is just a partial list of the rules associated with an IPS. Search the Help for more information on In-Process Servers.

Benefits of In-Process servers

The major benefit of an in-process server as opposed to an out-of-process server is SPEED! Since this runs as a DLL in the same process space, there is no cross-process communication that has to take place. This can significantly speed up the time it takes to invoke methods and set/read properties.

Summary

In this chapter, you learned to put together a template for all your data access classes. By putting together your data access classes to use the DBRecordset object, you can change your data access method within the DBRecordset object, and you don’t need to change the Data Access Class. This means as new ways of accessing data are introduced, you don’t need to rewrite your whole application, only a couple of classes.

A couple of other benefits are that you encapsulate business rules in one place. You have a common interface to a table. There is no need to write SQL code all over your application; you can put it all into one place.

In this chapter, you learned to create database connection classes for some other database access methods. By abstracting connections, your base classes that deal with tables can remain the same, and you only need to add the new connection class for the database access method you want to use.

You also learned to create Recordset classes for additional database methods. Once again, this helps you abstract dealing with result sets of data so that your base classes do not need to change.

You also learned how to create a code component out of the application built in the previous chapter.

© 1997 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft and Visual Basic are registered trademarks and ActiveX is a trademark of Microsoft Corporation.

Other product and company names listed herein may be the trademarks of their respective owners.