Microsoft Access VBA Macro Programming

Using the PNames Collection as a

Multitier Application

Having created a working object model based on your data services layer, you can now make

use of this in a further Access database that becomes the User Interface Application.

Create a new blank Access database. Open the VBE window by pressingALT+F11. Use

Tools | References on the VBE menu and browse to the object model database you created

earlier. Your database will not appear in the list since it is not a DLL or OCX file. Instead,

use the Browse button to locate your database. Click OK and then check the box for your

reference. Click OK and then click Save on the VBE menu.

Close the database and then re-open it. When you enter the VBE window, you will now

see your object model in the Project window.

Insert a module using Insert | Module on the VBE menu. Enter the following code:

Sub test_class()

Dim pn As MyPnames, n As Integer

Set pn = New MyPnames

MsgBox pn.Count

For n = 1 To pn.Count

MsgBox pn.Item(n).Pname

Next n

End Sub

Run this code and you will see message boxes giving you the number of records in the

collection and the Pname property for each record. This is working back through your object

model to the data services layer you created earlier in this chapter.

272 Microsoft Access 2010 VBA Macro Programming