Objects and Collections - MS Access

As you read through this book you will learn about many different objects and how to manipulate them. You will also find that objects are sometimes gathered together and held inside other objects called collections; a collection object holds a set of objects which are of a similar type. For example a Recordset contains a Fields collection and the Fields collection contains the set of Field objects in the Recordset.

Objects can have a number of properties and methods. A property is a characteristic that can be read or written; a method is something that you can call to manipulate the object.

Note

A quick way to determine that you are working with an object is to enable IntelliSense and then type in the name and press the ”.“ (period) key. If it is an object, IntelliSense displays a list of methods and properties associated with it.

Collections, by contrast, only have a few properties and methods.

Key Collection Properties:

  • Count Counts how many objects are in the set.

Collections in Access are 0-based: Collection(0), Collection(1)…Collection(Count-1). But in products like Microsoft Excel, collections are 1-based: Sheet(1), Sheet(2).. Sheet(Count). Key Collection Methods:

  • Add Adds an object.
  • Delete or Remove Removes an object. As an example, the TableDefs collection is the collection that contain all the tables in the database (both real tables and linked tables).

Figure shows some of the TableDef properties.

A TableDef has properties such as Attributes, DateCreated and Connect. It also has methods such as CreateField, CreateIndex and CreateProperty.

A TableDef has properties such as Attributes, DateCreated and Connect. It also has methods such as CreateField, CreateIndex and CreateProperty.

Objects are normally given singular names such as a TableDef, and the collections have plural names such as the TableDefs collection.

Object Variables

To refer to objects, we use object variables. In the preceding section, we defined a TableDef object, and we used the For … Each loop to run through the TableDefs collection getting a reference to each object in the collection.

Unlike a normal variable, which acts like a slot in memory in which values are held, an object variable is a reference in memory to an object (it is not a copy of the object); in many computer languages, this is called a pointer (because it points to an object).

If you need to create an object variable which points to a single object or collection, use the SET command. The code below uses two object variables, the db object variable references the current database and the tdef object variable is set to point at a specific table:

Tidying up object variables after use

Once the program code has finished execution, it is considered good practice to set any object variables to Nothing, which clears the reference in memory, some objects also have a Close method, and again it is considered good practice in this case to close the object variable.

Using the Close method and setting the variable to Nothing are not normally essential operations because once the code completes execution and the variables go out of scope, any tidy-up operations should be automatically taken care of; you might find that programmers omit these final tidy-up operations.

The New keyword which can be used to create a new object in memory, in the example below the New keyword is used to create a new table with a single field. Creating objects can be more complicated as there can be other rules to be observed, in this example once you have created objects, you need to save them (append) into collections:

Is Nothing, IsEmpty, IsObject

Object variables that have not been assigned to a valid object have a value of Nothing. You can use Is Nothing to determine whether the variable has been assigned a value, or an assignment failed. IsObject can be used to determine if a variable is an object variable, or if a variant variable has a vbObject type (error trapping is often required with this test). Examples of these functions are shown in the following code, with the results shown in Figure:

Is Nothing can be used to detect when an object variable has not been assigned a value.

Is Nothing can be used to detect when an object variable has not been assigned a value.

The previous example demonstrates how when you have finished using an object variable, you explicity set the variable to Nothing. This is considered to be good practice rather than assuming that the variable will be set to Nothing once it goes out of scope when the procedure ends.

Variant variables can be tested with the IsEmpty function to establish whether they have been assigned a value, the results of the following code are shown in Figure :

The results from using IsEmpty and VarType on variant variables.

The results from using IsEmpty and VarType on variant variables.

The preceding shows that using IsEmpty on a variable that is not a variant always returns false. The example also shows the use of the VarType function to determine what kind of data is contained in the variant variable. Note that assigning a different item of data to a variant variable can change the variant type.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics