Next, select theClass_Initializesubroutine. This routine runs when you first use the class
and determines what the contents will be. To run it, click the Class section in the top-left
drop-down and click Initialize in the top-right drop-down. Enter the following code:
Private Sub Class_Initialize()
Dim objPname As New MyPName
Dim MyConn As New ADODB.Connection, ReSet As New ADODB.Recordset
Dim ConStr As String
ConStr = "Driver={Microsoft Access Driver (.mdb, .accdb)};" &
"Dbq=MyTable.accdb;" &
"DefaultDir=C:\MyPath\;" & _
"Uid=Admin;Pwd=;"
MyConn.ConnectionString = ConStr
MyConn.Open
Set ReSet = MyConn.Execute("select * from MyTable")
Set mPnames = New Collection
Do Until ReSet.EOF
Set objPname = New MyPName
objPname.Pname = ReSet!MyNameReSet.MoveNextmPnames.Add objPname
Loop
End Sub
First, you define an object calledobjPnamebased on the object you created calledMyPName.
Notice that the objectsMyPNameandMyPNamesnow appear in the drop-down list as you
type the code in. As we are using ADO to communicate with the data services layer, we also
define objects for the ADO connection and the recordset (see Chapter 19 for more details on
working with external databases). We are using ADO here because it provides an efficient way
of connecting to an external database using a class module. If you use linked tables and DSNs,
there is a problem that these will have to be set up on whichever computer this example is being
run on.
A connection string called ConStr is created. This provides details of the driver to be used,
the name, and the location of the database. You can also include a user ID and password,
although in the case of your Access database, this is optional.
You then set the local variable,mPNames, as a newCollectionobject. At this point, it is
empty.
Chapter 21: Class Modules 269