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.
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:
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.
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.
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
A user defined type used to pass parameters to the function. Dim tCreateVBCode as CreateVBCode
sDBName = "..."
sTable = "..."
sClassPrefix = "..."
sClassSuffix = "..."
sQueryPrefix = "..."
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
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
Public Property Get sName() As String
sName = msName
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
.RsToObject Insert recordset values into object properties. .RsToObject rs, obj
Object Browser - Methods and Properties
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
Create ADO class project forms and modules.
ADO demo project forms and modules.
Template Project - Required minimum Forms and Modules.
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.
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