Microsoft Access VBA Macro Programming

property. For example, you could find out the number of fields in the USysRibbons table (see

Chapter 11 on how this table is built) by using the following code:

MsgBox CurrentDb.TableDefs("USyRribbons").Fields.Count

This will return the value of 2 (fields) for USysRibbons. However, the value is read-only

and you cannot alter it to create more fields. If you could do this, it would create tremendous

confusion and break the integrity of the table.

The designers of the Access object module have written rules saying, “You can view the

number of fields in a table but you cannot change the number directly.” In the same way, you

can design your own objects and set the rules accordingly about what can be done with those

objects—which properties and methods there will be and whether a collection can be changed

or whether it is read-only.

In the following example, you will create an object of names taken from cells in a

spreadsheet. The object collection will be calledPNames, and it will be a collection of

PNameobjects. The names could be the names of people or the names of places.

Creating a Data Services Layer

You must first create a separate Access database to act as the Data Service Layer for your

object model. Open a blank database, calling itMyData, and create a new table by clicking

Create in the Access menu bar. Then, click the Table Design icon in the Tables group of the

ribbon. Create a field calledMyNameand save the table with the name of MyTable. Enter

some data into the table to create a few records.

You could also use SQL Server or Oracle as the Data Service Layer, but for the purpose of

this example an Access database is used.

It is important to keep the Data Service Layer separate from the Object model you are

going to create, otherwise this will lead to locking problems.

Inserting a Class Module

Select Insert | Class Module from the VBE menu to create a class module called Class1. You

must change the name immediately, because this will be the name of the object you create,

and Class1 is somewhat meaningless because it does not reflect the functionality of that class.

You use this name within your code to refer to the object, and it will become tedious if you

have to keep referring to Class1.

Change the name in the Properties window. To view the Properties window, select View |

Properties Window on the VBE menu, or pressF4.

Select the name field and overtype it withMyPName. This represents the individual

objects within a collection. Double-click the class module within the tree view to display it.

Change theInstancingproperty to PublicNotCreatable. This makes your object visible to

other applications.

266 Microsoft Access 2010 VBA Macro Programming