Linking to the database

How do we link class modules with the tables in our database? Let's set up an elementary example where we read customer details from a table into a customer object.

1. Create a new table tblCustomers with the following fields:
    

   Field Name	Data Type	Setting
   CustomerID	AutoNumber	Primary Index
   CustomerName	Text
   Address	Text
   Telephone	Text
   DiscountRate	Number	Double
   Terms	Number	Integer

   The DiscountRate is the standard discount we might give each particular customer and Terms is the number of days credit we give them.
    

2. Enter some dummy data to create a couple of records. Make sure you don't leave any empty fields for the moment.
    

   tblCustomers: Sample Data

   CustomerID	CustomerName	Address	Telephone	DiscountRate	Terms
   1	Fred	123 Main Street	(02) 1234 1234	2.50%	30
   2	Sue	234 Any Road	(02) 2345 5666	5.00%	60

   
    
3. Create a new class module clsCustomer and add the code (clsCustomer.txt).
4. In the Debug Window, type the following commands (the responses are shown in italics):

   Set oCust = New clsCustomer
   ? oCust.Load(1)
   True
   ? oCust.customername
   Fred
   ? oCust.terms
    30 
   ? oCust.Load(2)
   True
   ? oCust.customername 
   Sue
   ? oCust.discountrate
    0.05 
   ? oCust.telephone
   (02) 2345 5666
   

What has this done?

• Created a new object oCust of class clsCustomer in memory with the command

      Set oCust = New clsCustomer
  

• Loaded the data in turn from the first two records of the database table tblCustomers into the object (and told us whether or not it was successful), e.g.

      ? oCust.Load(1)
  

• Printed out the values of the object's properties, e.g.

      ? oCust.CustomerName
  

What does this mean?

• One simple command oCust.Load(n) will go and load all the fields of the customer with CustomerID n into memory. You can do this in your normal code or in the Debug Window.
• All the code that handles access to the database is in the class module.
• You can refer to each property of your object with the syntax oCust.property. You can use these properties in your main code just like any other variable.
• You have a bit of coding to do at the start when you create the class module, but this is more than paid back in simplicity of coding in your main application.

In the next lesson, we'll show how to save your object's data back to the database.

Things to note in the class module code

• The use of a Private Constant in the class module code to refer to the database table name:

      Private Const scTABLE As String = "tblCustomers"
  

  This makes it easy if you change the name of the table or make a copy of the code for another object. It also prevents you from mis-spelling the table name elsewhere in your code.
• Using the dbOpenForwardOnly option to speed up data access from the table:

      Set rs = CurrentDb().OpenRecordset(sQry, dbOpenForwardOnly)
  

• Defining the function Load as Boolean and returning True if successful or False if it fails:

  Public Function Load(ID As Long) As Boolean
      Load = False
      
      ' Do the work... Goto Done if fail or error
      
      Load = True
  Done:
      Exit Function
  

• Making the CustomerID read-only to prevent accidental changes.
• My personal preference for naming the autonumber index field is always to use the full word CustomerID or OrderID instead of using just ID. This makes creating queries much simpler when you have to join tables. If you copy the code for the class module to create a new one, you can simply do a global edit to change the word "Customer" to, say, "Order".

<< Back  |  Next >>

Contact us

To contact us or comment on this page, please send us a message.

This page first published circa 2000. Last updated 9 September 2025.