Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Intermediate
Oct 6, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Generate Business Object Classes

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

	'CConnection is your generic Connection (or 
	'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, _


	'generate private variables
	Debug.Print _
		"'Private Data members for table: " & _

	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

	'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"

		'property set statement
		Debug.Print "Public Property Let " & _
			strName & "(in" & strName & " As " & _
			strType & ")"
		Debug.Print vbTab & "m_" & strName & _
			" = " & "in" & strName
		Debug.Print "End Property"
	Next fld

	Set rs = Nothing
End Sub

Private Function VBType(strFldType As String) As _
	'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.
Arnel J.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date