Class Libraries

team lib

Previously, we extolled the virtues of object orientation, and how classes can promote not only good programming, but also good code reuse as well. Since we've shown that using library databases is just another way of promoting code reuse, it seems sensible that that we should use classes within a library database. The unfortunate fact is, though, that although classes can be used outside of the database within which they reside, they cannot actually be instantiated . However, there is a way around this, because we know that normal procedures can be used outside of their own database, so we can provide a wrapper function in the library database that just instantiates the object, and passes it back to the calling object.

So, what we can't do is this:

click to expand

But what we can do is this:

click to expand

The class is exactly the same, but there is now a public function, in a normal module, that instantiates the class object. The disadvantage of this method is that you can't use early binding, since Access has no knowledge of what the class is. Despite this, though, it's a very good way of using classes in multiple databases.

An ADO Class Library

Although we're concentrating on DAO in this book, there are some things that ADO can do easily that DAO can't, and one of these is the User Roster. This allows us to see which users are logged into a database - extremely useful if you need to make changes or shut down a server. A set of support libraries was shipped with older versions of Access, but the ADO way is much simpler.

If you want to use ADO, you know that you need to have the ActiveX Data Objects reference set in the VBE, and the same applies with DAO, where you need the Data Access Objects reference set. Having the two set together isn't a problem, but can lead to some confusion. For example, consider the following line of code:

 Dim recIces As Recordset 

Does this refer to a DAO Recordset or an ADO Recordset ? In fact, if you don't specify the data access method, then DAO is the default. To clarify which method you want to use, you can prefix the object with its type:

 Dim recIces As DAO.Recordset Dim recIces As ADODB.Recordset 

This makes it very clear, but it looks a little ugly. If all we are using ADO for the user roster, then it seems sensible to put this into a class library. That way the class library can have the reference to ADO, so we don't need this reference in our database, which makes things simpler. In real life, however, it is preferable to simply explicitly define which data access method you are using, so that if the code gets copied into another library, there is no confusion (or compile error). For our purposes though, we are going to create a new library.

Try It Out-Creating a Class Library

  1. Create a new database called MiniADO .

  2. Create a new class module (from the Insert menu), and add the following code:

       Private Const JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"     Public Sub GetUserRoster(vUserList As Variant, lReturnType As ReturnType)     Dim conADO As ADODB.Connection     Dim recADO As ADODB.Recordset     Set conADO = CurrentProject.Connection     Set recADO = conADO.OpenSchema(adSchemaProviderSpecific, , _     JET_SCHEMA_USERROSTER)     If lReturnType = RETURN_ARRAY Then     vUserList = recADO.GetRows     Else     vUserList = recADO.GetString(adClipString, , ",", vbCrLf)     End If     recADO.Close     conADO.Close     End Sub   
  3. In the Properties window, set the class Name to clsADO and the Instancing to 2 - PublicNotCreatable .

  4. Save this module as clsADO.

  5. Create a normal module, and add the following code:

       Public Enum ReturnType     RETURN_ARRAY     RETURN_STRING     End Enum     Public Function CreateMiniADO() As clsADO     Dim cADO As New clsADO     Set CreateMiniADO = cADO     End Function   
  6. Save this module as MiniADOEntryPoints .

  7. Close this database and open IceCream.mdb .

  8. Add a reference to MiniADO.mdb as before.

  9. In the code module for this chapter, add a new procedure:

       Public Sub WhosIn()     Dim objMiniADO As Object     Dim varUsers As Variant     Set objMiniADO = CreateMiniADO     objMiniADO.GetUserRoster varUsers, RETURN_STRING     Debug.Print varUsers     End Sub   
  10. From the Immediate window, run this procedure:

    click to expand

This shows only one user logged in. The columns (separated by commas) are: Computer Name, Login Name, Connected, and Suspect State, although you can only see the first one here.

  1. Now open another copy of the same database and run the procedure again:

    click to expand

The above diagram shows two machines accessing the database: the first machine is called CATHERINEA and the second machine is called EWANB . At this stage, you might not think this is too useful, since both users are Admin. However, that's only because we haven't set up security. Once you look at Chapter 17 on multi-user issues, you'll see how to set up security to allow users to log into an Access database under user names . When no security is involved, all users are Admin, although the machine name shows up correctly. So, even if you aren't using security, you can still get a good user list if you name your machines correctly.

How It Works

Let's look at the class first. At the top of the class we define a constant, which is the special key, called a GUID. A GUID ( G lobally U nique ID entifier) is a special number that is unique across the whole world, and this is used by the operating system to identify objects, components , libraries, and so on. Generally you don't need to know about them, as most objects have a proper name, but this user roster doesn't have a name - just a GUID.

 Private Const JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}" 

Next comes the definition of the method, taking two arguments. The first is a variant, which will hold the user details, and will either be returned containing a string or an array. The second argument identifies whether a string or an array of the user details is to be returned.

 Public Sub GetUserRoster(vUserList As Variant, lReturnType As ReturnType) 

Now we have two ADO objects - one for the Connection to the database, and one for the Recordset of connected users.

 Dim conADO As ADODB.Connection    Dim recADO As ADODB.Recordset 

Next, we connect to the current database. The CurrentProject object is an Access object that points to the currently open database - not the library database. The Connection property of this object contains the ADO connection details.

 Set conADO = CurrentProject.Connection 

Now that we are connected to the database, we need to get the users, and for that we use the OpenSchema method. Schemas are really just collections of like objects, and most databases have schemas for tables, queries, users, and so on - they describe what the data is and how it is stored. ADO has provided this method to access these details, and it also allows the data provider (in this case the JET database engine) to specify its own schemas as well as the default ones. This is done using adSchemaProviderSpecific as the first argument of the OpenSchema method. The second argument allows us to filter the results - we've left this empty because we want all of the users returned. The third argument is the special key that tells the data provider (JET) what to return.

 Set recADO = conADO.OpenSchema(adSchemaProviderSpecific, , _       JET_SCHEMA_USERROSTER) 

At this stage recADO contains a recordset of the user details. We can't just return this recordset, because the whole purpose of this class is to encapsulate ADO, allowing it to be used from another database without setting ADO references. So we want to return the recordset as a comma-separated string, or as an array. That's where the second argument to the GetUserRoster procedure comes in - you'll see where the values are defined in a minute.

If the return type is to be an array, then we call the GetRows method of the recordset. This is exactly the same as the GetRows method of the DAO recordset, and converts a recordset into an array. We assign the result of the GetRows method to the variant parameter passed into the GetUserRoster procedure - remember that this is a Variant , and variants can hold different variable types.

 If lReturnType = RETURN_ARRAY Then       vUserList = recADO.GetRows 

If the return type isn't to be an array, then it must be a string. In this case we use the GetString method of the recordset, which converts a recordset into a string. The two important arguments to this method are the last two. The first of these identifies the separator (or delimiter ) between the fields, in this case a comma, and the latter identifies the separator between the rows, in this case a carriage return and new line.

 Else       vUserList = recADO.GetString(adClipString, , ",", vbCrLf)    End If 

The last thing to do is close both the recordset and the connection.

 recADO.Close    conADO.Close End Sub 

That's it for the class - it has just one method. One thing to notice is the setting for the Instancing property of the class - we set this to 2 - PublicNotCreatable . This defines the class as Public , meaning it can be used outside of the database in which it is defined, but that it cannot be created in another database. Unfortunately Access doesn't allow Public Creatable classes, where they can be instantiated and used outside of their own database. It's for this reason that we have to have a normal module with a public procedure to create the object.

Let's now look at the normal module associated with the class. Firstly there's an Enum statement, to define the two possible return types for the list of users.

 Public Enum ReturnType    RETURN_ARRAY    RETURN_STRING End Enum 

Now we have a Public function, which returns a type of the MiniADO class. In this function we simply instantiate a new object of that class type, and then return this object.

 Public Function CreateMiniADO() As clsADO    Dim cADO  As New clsADO    Set CreateMiniADO = cADO End Function 

So, at this stage we have finished the class library. We have a class with one method, and a function that creates an instance of the class for us. Let's look at how it's used now, in the WhosIn procedure.

Firstly there are two variables . The first is a generic object, which will hold the MiniADO class. Remember that we can't define this as clsADO because the class can't be seen outside of its own database, and it's in the library database. The second variable is a variant that will hold the user details.

 Dim objMiniADO As Object    Dim varUsers As Variant 

To instantiate the class, we have to call the public function in the library database. This creates the class object and returns it to us.

 Set objMiniADO = CreateMiniADO 

Then we call the GetUserRoster method of the class.

 objMiniADO.GetUserRoster varUsers, RETURN_STRING 

Finally, we can print out the user details.

 Debug.Print varUsers 

If you want to return an array of the user details all you have to do is change RETURN_STRING for RETURN_ARRAY , and after the GetUserRoster method is called, varUsers will hold an array of the details. In this case, you would use the normal array indexing methods to access the details:

 Debug.Print varUsers(0,0) 

This would print out the machine name of the first user. Remember that this array is like a recordset, so the first array index is the rows, and the second index the columns.

As you can see, creating class libraries is fairly simple. The only downside is the instancing problem, but using a function to create the class object can circumvent this. We used this method here to encapsulate some ADO functionality, and another good area is API functions, or general programming libraries.

An Undocumented Secret

Having shown you the official method of using classes located in libraries (the wrapper function to return a pointer to the class), we will now discuss another method that is officially unsupported, and is undocumented, but widely used.

Class modules have some properties that can't be seen or manipulated from inside the VBA editor. However, any module can be exported to a text file, and from a text editor, we can open that exported module and see and manipulate the properties that can't be manipulated from inside Access. Two of these properties will allow us to modify our class modules such that they can be directly used from our front end while residing in a class library, including directly instantiating them, using early binding, and so on.

The first thing we need to do is export the class to a text file. To do this, click on the class that you want to export and choose Export from the file menu. The Export Module As dialog will open.

click to expand

Notice that I started by exporting clsADO , but added Exposed to the end of the name. In the next step, we will pull this text file back into our library and it can't have the same name as the original class module. Make sure that you change the Save as type to Text Files , and then add a .txt to the end of the file name. Click Export and you will have saved the class module to the text file in the directory you selected. I have added a Classes directory under My Documents to hold all the class files that I export.

Once you have exported the class module to a text file, you can open the file with Notepad. When you do so, you will notice a bunch of lines above the normal Option statements that are usually at the top of the class when viewed from inside Access.

click to expand

The ones we are specifically interested in are:

Attribute VB_Name = "clsADO"

Attribute VB_Creatable = FalseAttribute VB_Exposed = True

Using Notepad, we set the VB_Name to whatever we want the class to be called inside Access. The other two we will set to True . Notice that in this case VB_Exposed is already True , but this won't necessarily be the case with other classes that you might want to use this method on, and both properties must be set to True for this trick to work.

click to expand

Save the text file and close Notepad.

In order to get the class text file back into the Access library, we need to insert a new class module (from the Insert menu), then choose File from the Insert menu. We use the Insert File dialog to navigate to and select the text file we just created, and then click Open .

click to expand

Doing so will import the text module into the new class module.

click to expand

We have a little cleanup to do. Select and delete everything from the top Option Compare statement down to above the second Option Compare , then save the class module.

You're done!

To recap, for reasons known only to them, Microsoft does not allow us to see these class "properties" from inside the VBA editor (though they can be seen from inside VB's (the language) editor. Therefore, in order to work with them in Access, you exported the class to a text directory on the hard disk, which allows you to edit the class in an external editor. You edited these two class properties to set them to True . You then imported them back into Access, cleaning up some stuff that the import leaves exposed, and which, if left exposed, would cause compile errors. Because you have edited those hidden properties to True , you can now reference the class directly outside of the library.

Let's look at how it's used now, in the WhosInExposed procedure.

As before, there are two variables. The first is a typed class variable, which will hold the MiniADO class. Remember that we can now define this as clsADOExposed because the class can be seen outside of its own database (the library database). The second variable is a variant that will hold the user details.

   Dim clsMiniADO      As clsADOExposed     Dim varUsers        As Variant   

To instantiate the class we no longer have to use (or have) the Public function in the library database. We simply use the normal Set MyVar = new ... syntax

   Set clsMiniADO = new clsADOExposed   

Then we call the GetUserRoster method of the class.

   clsMiniADO.GetUserRoster varUsers, RETURN_STRING   

Finally, we can print out the user details.

   Debug.Print varUsers   

So there you have it, an undocumented way to expose classes so that they can be seen outside of the library that they reside in. Doing this gives us all the advantages of direct instantiation: early binding and IntelliSense. Using the "object and wrapper" method, we can not get any help from Access in seeing the names of methods or properties of the classes in our libraries, whereas with the new method we have code completion as well as the parameter prompting that occurs with early binding. In addition, we no longer have to build wrappers for the classes that we place into libraries. All in all, a very useful trick to have up our sleeve.

team lib

Beginning Access 2002 VBA
Beginning Access 2002 VBA (Programmer to Programmer)
ISBN: 0764544020
EAN: 2147483647
Year: 2003
Pages: 256 © 2008-2017.
If you may any questions please contact us: