Create a VB Class to Process an Access Table using ADO - February 2003

Recently I published a tip that creates VB 6 code to process an Access database using DAO. Click here to view that tip. This month I updated the code to use ADO. Unlike the DAO tip that created a text file whose code was pasted into a class module the ADO version creates the actual class module - no more cutting a pasting required. Methods include: LoadAll, LoadUsingGUID, Insert, Update, Delete, ObjectToObjectCopy, ObjectToObjectDiffer, ObjectToParameters and RSToObject. The generated code creates properties and SQL statements to process the table. It also creates the stored procedures.  In August 2008 I converted the tip to use ADO to process a SQL table - click here to view that tip.

The Demo Program

I created two VB 6 programs to show how to use function 'CreateVBCode'. The first [projCreateVBCodeUsingADO.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. The class module is created in app.path. A screen shot:

Generated class module code.

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

Table - Table 'MySampleTable' is created with 8 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 'clsMySampleTableBase.cls'.

Sample Table - The sample table:

Sample Table
The 'sample' table.

Stored Procedures - The generated stored procedures - queries:

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

Warning Message - If the class module already exists then a warning message is issued. You can bypass the creation of the class module by replying appropriately to the 'warning' dialog.

Warning Message

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

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.

bYesNo - A binary field. Shows how to save and retrieve a 'yes/no' field.

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
Public Type udtCreateVBCode
  sDBName As String
  sTable As String
  sClassPrefix As String
  sClassSuffix As String
  sFileName As String
  sQueryPrefix As String
  bNoBuildClassModules As Boolean
  bNoStoreQueries As Boolean
End Type
A user defined type used to pass parameters to the function. Dim tCreateVBCode as CreateVBCode
With tCreateVBCode
  sDBName = "..."
  sTable = "..."
  sClassPrefix = "..."
  sClassSuffix = "..."
  sQueryPrefix = "..."
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 use to create the class module name. cls
sClassSuffix This suffix is added to the table name. Base
sFileName Upon return this field contains the name of the file in which the generated code is stored. c:\MyProject\clsMySampleTableBase.cls
sQueryPrefix Added to the name of the stored procedure. Default is sClassPrefix. CVBC
bNoBuildClassModules If True don't build the class module. False/True
bNoStoreQueries If True don't store the generated queries in the database. False/True

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. If .LoadAll(mConn, mcolMySampleTables) Then
.LoadUsingGUID Load the single record, identified by its GUID, into a collection. If .LoadUsingGUID(mConn, mcolMySampleTables) Then
.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.
If .Insert(mConn) Then
.Update Update the record identified by its GUID. If .Update(mConn) Then
.Delete Delete the record identified by its GUID. If .Delete(mConn) Then
.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)
.ObjectToParameters Insert object values into ADO parameters. Set cmd = cat.Procedures("CVBC Update MySampleTable Using GUID").Command
.ObjectToParameters cmd
.RsToObject Insert recordset values into object properties. .RsToObject rs, obj

Object Browser - Methods and Properties

Pseudo Inheritance

I have demonstrated 'pseudo inheritance' in class module 'clsMySampleTable.cls'. VB 6 does not directly support inheritance. In this example I created a 'base' class automatically - then I created a pseudo inherited class which uses some of the methods in the 'base' class - for example method RSToObject to move data from a recordset to a collection of the base class object. The advantage - I can make 'manual' changes to the inherited class without affecting the base class - meaning that if I add fields to a table I can 'rerun' the base class creation process and not affect the 'manual' changes made to the 'inherited' class.

The Programs Use These Modules

Project Elements
Create ADO class project forms and modules.

ADO Demo
ADO demo project forms and modules.

Template Project
Template Project - Required minimum Forms and Modules.

ADO and ADOX References
Be sure to reference ADO and ADOX. Both libraries are used by CreateVBCode.

Module - Click for More Info Description Used in the Demo To
modCreateVBCodeUsingADO.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. A warning message is issued if it does not. Used in the first program to create the class module's code.
modADOTypeToString Converts and ADO Type to a String.  
modCreateGUID.bas Create a GUID. See Generate GUID for details. Creates a GUID.
clsConvertDataType.cls Convert a data type from ADO to VB and back. Also provides support for other applications, such as SQL and Oracle. Not verified for all types.  
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.
clsSaveMemoADO.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.
modIsAuthor.bas Is the author running the application.  
modJetOLEDBConstants.bas Jet OLEDB constants. Not used in this demo but provided for reference. No used.
modLocationLocationLocation.bas Form positioning routines.  
modMouseSet.bas Provides a mouse 'stack'.  
modOKToCreateFile.bas Provides support for 'overwrite protection' of class modules.  
modSetFileReadWrite.bas If overwriting a file set to allow 'overwrite'.  
TBRegistry.bas Read and write to the registry.  
modTempName.bas Provides a temporary file name.  
modWhereAuthor.bas Works with IsAuthor. Supplies the author's location.  

Updated 2003/02/19

2003/02/19 - The 'UPDATE' query was not created correctly if the last field in a table was a memo field. Fixed. If you downloaded the source before this date you may want to download it again.

Download VB 6 Source Code

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

Previous Tip   Tip Index   Next Tip   First Tip


Updated 2009/03/29