Create a VB Class to Process an Access Table using DAO - September 2002

Some years ago I wrote a function I call 'CreateVBCode' which creates VB code for a class module than can be used to process a DAO table. Methods include: LoadAll, LoadUsingGUID, Insert, Update, Delete, ObjectToObjectCopy and ObjectToObjectDiffer. The generated code creates properties and SQL statements to process the table. It also creates the stored procedures needed to process the table. I recently updated the code and decided to make it the September 2002 Tip-of-the-Month. In February 2003 I updated the tip to use ADO - click here to view that tip. In August 2008 I converted the tip to use ADO to process a SQL table - click here to view that tip.

2002/11/06 - Fixed a bug - If the last field name is "GUID" the generated UPDATE query and SQL statements contained an extra comma which caused Jet to reject the query. If you downloaded the code before this date you should download it again.

2003/01/14 - Allow for very long field names while generating SQL statements. 'Pasted In' generated VB6 code could exceed the maximum allowed VB6 line length. Each field is now created on a separate line.

The Demo Program

I created two VB 6 programs to show how to use function 'CreateVBCode'. The first [projCreateVBCode.vbp] creates a database [DAO 3.6 version 4, Access 2000 format], with one table and a few fields. For more information about DAO 3.6 link to: What I Learned About DAO 3.6 and Jet 4.0. The function generates class module code used to process the table. It creates stored procedures. And a text file of the generated code is created in app.path. A screen shot:

Generated Class Module Code
Generated code - copy it into a class module.

Database - Sample database 'MySampleDB.mdb' is created dynamically in app.path.

Table - Table 'MySampleTable' is created with 7 fields in the sample database. The 'key' field is a GUID. The GUID is also used as the key for storing the record in a collection.

Generated Code - The generated code is shown in the text box. It is also written as a file in app.path. In this example the file name is 'VBCode for MySampleTable.txt'.

Sample Table - The sample table:

Generated Table
The 'sample' table.

Stored Procedures - The generated stored procedures - queries:

Stored Procedures
Generated Stored Procedures [Queries]. They are prefixed by 'CVBC'.

The second demo program [projCreateVBCodeDemo.vbp] shows how to use the generated class module code. A screen shot:

Using the Generated Code
A 'tiny' application that uses the generated class module.

ListView - The listview contains a list of names sorted by sequence number.

GUID - The GUID is automatically created when the object is instantiated. A required field. Module 'modCreateGUID.bas' is used. See below.

sName - A name. A text field. When the add button is clicked default name '<name>' is created. Change it to any name.

lSequence - A long integer. Can be any number and affects the sort sequence.

sMemoField - I included a memo field in the database to show how to handle updating a memo field. A memo field can't be saved directly using an Update query. Some months ago I wrote a tip that shows how to save a memo field. See below for a link to that tip. The length of the memo field is also shown.

dDateCreated - The date the object is created is automatically added to the object and will be stored, unless overwritten, when the object is saved to the database. A required field. There is no need to change this value.

dDateLastUpdated - The date the object was last updated. Originally it is the same as the date created. You need to change this date during the 'update' process. A required field.

sUpdatedBy - Typically the network user name. In this demo class module 'clsNetUserName' is used to retrieve the signed-on user name. You need to change this field during the 'update' process.

Using CreateVBCode

Function, Structure and Arguments Description Sample
CreateVBCode Call this function to generate the class module code. CreateVBCode tCreateVBCode
Type CreateVBCode
    sDBName As String
    sTable as String
    sClassPrefix as String
    sFileName As String
End Type
A user defined type used to pass parameters to the function. Dim tCreateVBCode as CreateVBCode
With tCreateVBCode
    sDBName = "..."
    sTable = "..."
    sClassPrefix = "..."
End With
sDBName The fully qualified database name. Is opened in function CreateVBCode. c:\MyProject\MySampleDB.mdb
sTable The name of the table for which code is to be generated. MySampleTable
sClassPrefix This prefix is added to the table name and is used to create the class module name. cls
sFileName Upon return this field contains the name of the file in which the generated code is stored. c:\MyProject\VBCode for MySampleTable.txt

Generated Code

Properties Description Sample
One for each field. A 'Let' and 'Get' property is created for each of the table's fields. An 'sKey' field is also created. Private msName As String
Public Property Let sName(sName As String)
    msName = sName
End Property
Public Property Get sName() As String
    sName = msName
End Property
Methods The following exposed methods [functions] are created: Calling Sample
.LoadAll Load the table into a collection. Dim objMySampleTable As New clsMySampleTable
Dim colMySampleTables  as Collection
With objMySampleTable
    Set mcolMySampleTables = New Collection
    If .LoadAll(mDB, mcolMySampleTables) Then
    End If
End With
.LoadUsingGUID Load the single record, identified by its GUID, into a collection. Dim objMySampleTable As New clsMySampleTable
Dim colMySampleTables  as Collection
With objMySampleTable
    .GUID = "{4FA6C56B-8948-43BC-AB7F-EF0E414266B3}"
    Set colMySampleTables = New Collection
    If .LoadUsingGUID(mDB, mcolMySampleTables) Then
    End If
End With
.Insert Insert the object into the database. Note that the GUID,
dDateCreated and dDateLastUpdated are stored
in the object when it is created during the Class_Initialize method.
Dim objMySampleTable As New clsMySampleTable
Dim objNetUserName As New clsNetUserName
With objMySampleTable
    .sName = "<name>"
    .sKey = .GUID 'use the guid as the key
    .lSequence = {some sequence number}
    .sUpdatedBy = objNetUserName.NetworkUserName
    If .Insert(mDB) Then
    End If
End With
.Update Update the record identified by its GUID. With objMySampleTable
    .sName = Trim$(Me.Text1(1).Text)
    .lSequence = CLng(Me.Text1(2).Text)
    If .Update(mDB) Then
    End If
End With
.Delete Delete the record identified by its GUID. With objMySampleTable
    If .Delete(mDB) Then
    End If
End With
.ObjectToObjectCopy Create a duplicate of the object. .ObjectToObjectCopy objMySampleTableDup, objMySampleTable
.ObjectToObjectDiffer Compare the objects. Report true if they are the same. Dim bDoUpdate As Boolean
bDoUpdate = .ObjectToObjectDiffer(objMySampleTable, objMySampleTableDup)

The Demo Programs Use These Modules

Module - Click for More Info Description Used in the Demo To
modCreateVBCode.bas The module described in this tip. Add this to your application then call 'CreateVBCode' to create the class module's code. One of the fields in your table should be named GUID defined as a text field. Used in the first program to create the class module's code.
modCreateGUID.bas Create a GUID. See Generate GUID for details. Creates a GUID.
modAddBackSlash.bas Add a backslash to a path if needed. Used to add a backslash to app.path if needed.
clsNetUserName.cls Get the network user name and computer name. Used to update the 'sUpdatedBy' field.
clsSaveMemo.cls Save a memo field. See Save Memo Field for details. Used to update the 'sMemoField' field.
modConnectToWebSite.bas Connect to a web site Show this info when Help/Info is selected from the demo.
modInfoAbout.bas Info and about help. Provide help via the web and 'about' info.

Download VB 6 Source Code

Click here to download the VB 6 source code [27k]. Click here to view a tip that uses ADO rather than DAO.

Previous Tip   Tip Index   Next Tip   First Tip


Updated 2008/08/14