We are looking for some qualified individuals to work on a new Visual Basic site. The first task is developing a high-quality resource guide for Visual Basic developers. This resource guide will be a well-organized list of web sites that have very good resources about Visual Basic (from beginners stuff through advanced with topics like MTS and ASP). It will gradually expand to include other resources such as free source code and online shopping of development tools. If you are interested in working on a web site dedicated to Visual Basic jump to http://www.allvbstuff.com and fill out the form online. Thanks!
This issue demonstrates one method to encapsulate a database table inside a class module.
This class is a sample Employee class that shows several methods for encapsulating a database table inside a class module. Clearly this is just a starting point, but it lays down some good groundwork to enhance in many ways depending on the type of data you are manipulating.
The advantage with this approach that we have not seen elsewhere is the use of the enumerator for the fields in the table. It allows you to use the Auto List Members option to automatically show the fields available in the class when you use the Value property. You no longer have to remember the exact field names which can help eliminate some bugs due to typos. This also make it fairly painless to change a field name since all your code should only reference this class when accessing this table. In a future issue we will show how to automatically generate this class source code using Visual Basic to make managing your tables very easy.
NOTE: This class does not yet support adding new records. It is currently designed to manage existing records. This will also be covered in a future issue (most likely next week).
Public Enum enumEmployeeFields
This enumerator should reflect the fields you wish to be available from this class. The default names should be the field names in the database unless the names in the database tables are not easily understandable.
Public Property Set DatabaseObject(db As Variant)
This property allows you to set the Database object directly with an already opened database. Using this method could save some time by avoiding an additional call to open the database.
Public Property Let DatabaseObject(db As Variant)
This property allows you to set the Database object by passing the name of the database to open. You should only use this if you only plan on using this object once. A sample parameter would be "d:\source\employee.mdb"
Public Property Get DatabaseObject() As Database
You can use this to retrieve the database object that the class is currently working with.
Public Property Get Value(eFieldID As enumEmployeeFields) As Variant
This property allows you to retrieve a value previously loaded by the LoadFields method.
Public Property Let Value(eFieldID As enumEmployeeFields, vValue As Variant)
This property allows you to change a value previously loaded by the LoadFields method.
Public Sub LoadFields(lID As Long)
Once you set the DatabaseObject property you can call this method to retrieve all the fields in the table this class is managing (in this case it would be the employee table). The lID property is the unique id value for this record. You can create additional Load methods to retrieve the records, such as by username or last name.
Public Sub SaveFields()
If you wish to save any changes made by setting the value property to other values you need to call this method. This method will commit any changes made to the record to the database. If someone else has modified this record since the LoadFields method was called it will overwrite those changes. An enhancement here could be to check the Date Updated field to see if the record has been changed before writing it to the database.
This source code will change the first and last name fields for employee id number 15 to David and Smith respectively.
Dim emp As New cEmployee
emp.DatabaseObject = "d:\source\addins\dbclassmaker\employeesample.mdb"
' normally you would get this value from a table or some other input method.
emp.LoadFields 15
' print out current values
Debug.Print emp.Value(empFirstName)
Debug.Print emp.Value(empLastName)
' set new values
emp.Value(empFirstName) = "David"
emp.Value(empLastName) = "Smith"
' save them.
emp.SaveFields
To see the source code for this issue you must be a subscriber to Code of the Week. If you are a subscriber the source code is available at the following address: http://www.codeoftheweek.com/membersonly/bi/0093.html