Linking to the database
<< Back to Classes index page
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.
- 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.
- 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
- Create a new class module clsCustomer and add the code (clsCustomer.txt).
- 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?These commands have in turn:
- 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.
- Printed out the values of the object's properties, e.g.
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".
Any comments, feedback, questions: please send us a message.
This page first published circa 2000. Last updated 11 June 2014