Home » Tip Bank » Security » Visual Basic

Language: Visual Basic
Expertise: Intermediate

Oct 5, 1999

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 = Nothing
End Sub

Private 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 Select
End Function

If I want to write a class for the Person table, for example, I go to the Debug window and type:

 
GenTableClass "Person"

I then cut and paste the generated code to my target class module.