I often include a module called Utilities.bas in my projects. It contains routines that are not directly used by the application, but instead are invoked in the Debug window, usually to generate code snippets for the project.
For example, I have a routine that generates the skeleton of a class whose properties are mapped to the fields of a table, view, or stored procedure-as in the Business Object class described in Deborah Kurata’s book Doing Objects in Microsoft Visual Basic 5.0 (pp. 476-479, Que, 1999, ISBN: 1562765779). Writing the code for this class is tedious, especially when the table has many fields. Imagine writing all the Get and Let property subs for a table with 20 or 30 fields:
Sub GenTableClass(strTableName As String) Dim ccon As CConnection Dim sql As String Dim rs As Recordset Dim fld As Field Dim strName As String Dim strType As String 'connect 'CConnection is your generic Connection (or 'Database) 'class that connects to your database 'when initialized. In my case, this class has 'a property called Conn that returns a 'Connection object. ' Set ccon = New CConnection sql = "select * from " & _ strTableName & " where 1=2" 'no need to return a row Set rs = ccon.Conn.OpenRecordset(sql, _ dbOpenSnapshot) Debug.Print 'generate private variables Debug.Print _ "'Private Data members for table: " & _ strTableName For Each fld In rs.Fields strName = fld.Name strType = VBType(fld.Type) 'prefix is your choice Debug.Print "Private m_" & strName & _ " as " & strType Next fld Debug.Print 'Now, generate let and get property subs For Each fld In rs.Fields strName = fld.Name strType = VBType(fld.Type) 'property get statement Debug.Print "Public Property Get " & _ strName & "() as " & strType Debug.Print vbTab & strName & " = " & _ "m_" & strName Debug.Print "End Property" Debug.Print 'property set statement Debug.Print "Public Property Let " & _ strName & "(in" & strName & " As " & _ strType & ")" Debug.Print vbTab & "m_" & strName & _ " = " & "in" & strName Debug.Print "End Property" Debug.Print Next fld rs.Close Set rs = NothingEnd SubPrivate Function VBType(strFldType As String) As _ String 'You may have your own conversion Select Case strFldType Case dbChar, dbMemo, dbText VBType = "String" Case dbByte, dbInteger VBType = "Integer" Case dbCurrency VBType = "Currency" Case dbLong VBType = "Long" Case dbDate, dbTimeStamp, dbTime VBType = "Variant" Case dbFloat, dbDouble, dbNumeric VBType = "Double" Case dbSingle VBType = "Single" Case dbBoolean VBType = "Boolean" Case Else VBType = "Variant" End SelectEnd Function
If I want to write a class for the Person table, for example, I go to the Debug window and type:
I then cut and paste the generated code to my target class module.