jwcolby54
Active member
- Local time
- Today, 14:33
- Joined
- May 19, 2025
- Messages
- 320
I have been thinking about how to build tables for my book, and how that might fit into the purpose of the book, learning about how to use classes. I want to build some pretty specific little tables. I'll get into the why in a minute. For now, know that I didn't write the following code, I guided ChatGPt, and it was an iterative process. You don't even want to see the entire chat!
I started with wanting to define the name of the table, the name of the fields, and their data types. For this I defined a class. Eventually as I got comfortable with ChatGPT's ability to do this stuff (and in can do this stuff). I added in things like "make a field an autonumber, add an index, add a default value" etc.
So the field class I defined looks like:
As you can see it is simply a bunch of variables which define the fields and various properties of a field, plus property get / lets for all of it. And no I am not going to get into the whole "why not public" thing. Called oddly enough clsFieldDef.
After that I needed a class to build a table. Called oddly enough clsTableBuilder. The idea is to build a class which can accept a table name, and which has a class factory to allow me to create instances of clsFieldDef. These clsFieldDef instances get stored in a collection. Once I have defined the clsFieldDef instances for every field, I then call a function which builds the table and all of the fields, iterating the collection.
And yes, this code actually works. Test code:
I started with wanting to define the name of the table, the name of the fields, and their data types. For this I defined a class. Eventually as I got comfortable with ChatGPT's ability to do this stuff (and in can do this stuff). I added in things like "make a field an autonumber, add an index, add a default value" etc.
So the field class I defined looks like:
Code:
Option Compare Database
Option Explicit
Private mStrName As String
Private mIntType As Integer
Private mBlnIsAutoNumber As Boolean
Private mBlnIsIndexed As Boolean
Private mBlnIsPK As Boolean
Private mIntSize As Integer
Private mVarDefaultValue As Variant
Private mBlnIsRequired As Boolean
Public Property Get pName() As String
pName = mStrName
End Property
Public Property Let pName(ByVal lStrValue As String)
mStrName = lStrValue
End Property
Public Property Get pType() As Integer
pType = mIntType
End Property
Public Property Let pType(ByVal lIntValue As Integer)
mIntType = lIntValue
End Property
Public Property Get pIsAutoNumber() As Boolean
pIsAutoNumber = mBlnIsAutoNumber
End Property
Public Property Let pIsAutoNumber(ByVal lBlnValue As Boolean)
mBlnIsAutoNumber = lBlnValue
End Property
Public Property Get pIsIndexed() As Boolean
pIsIndexed = mBlnIsIndexed
End Property
Public Property Let pIsIndexed(ByVal lBlnValue As Boolean)
mBlnIsIndexed = lBlnValue
End Property
Public Property Get pIsPK() As Boolean
pIsPK = mBlnIsPK
End Property
Public Property Let pIsPK(ByVal lBlnValue As Boolean)
mBlnIsPK = lBlnValue
End Property
Public Property Get pSize() As Integer
pSize = mIntSize
End Property
Public Property Let pSize(ByVal lIntValue As Integer)
mIntSize = lIntValue
End Property
Public Property Get pDefaultValue() As Variant
pDefaultValue = mVarDefaultValue
End Property
Public Property Let pDefaultValue(ByVal lVarValue As Variant)
mVarDefaultValue = lVarValue
End Property
Public Property Get pIsRequired() As Boolean
pIsRequired = mBlnIsRequired
End Property
Public Property Let pIsRequired(ByVal lBlnValue As Boolean)
mBlnIsRequired = lBlnValue
End PropertyAs you can see it is simply a bunch of variables which define the fields and various properties of a field, plus property get / lets for all of it. And no I am not going to get into the whole "why not public" thing. Called oddly enough clsFieldDef.
After that I needed a class to build a table. Called oddly enough clsTableBuilder. The idea is to build a class which can accept a table name, and which has a class factory to allow me to create instances of clsFieldDef. These clsFieldDef instances get stored in a collection. Once I have defined the clsFieldDef instances for every field, I then call a function which builds the table and all of the fields, iterating the collection.
Code:
' === ENUM FIELDTYPES ===
Public Enum FieldTypes
ftBigInt = dbBigInt
ftBinary = dbBinary
ftBoolean = dbBoolean
ftByte = dbByte
ftCurrency = dbCurrency
ftDate = dbDate
ftDecimal = dbDecimal
ftDouble = dbDouble
ftGUID = dbGUID
ftInteger = dbInteger
ftLong = dbLong
ftMemo = dbMemo
ftSingle = dbSingle
ftText = dbText
ftVarBinary = dbVarBinary
End Enum
' === MEMBER VARIABLES ===
Private mStrTableName As String
Private mColFields As Collection
Private mDbDatabase As DAO.Database
Private mTdfTableDef As DAO.TableDef
' === INITIALIZATION ===
Private Sub Class_Initialize()
Set mDbDatabase = CurrentDb
Set mColFields = New Collection
End Sub
Private Sub Class_Terminate()
Set mTdfTableDef = Nothing
Set mDbDatabase = Nothing
Set mColFields = Nothing
End Sub
Public Sub Init(ByVal lStrTableName As String)
mStrTableName = lStrTableName
End Sub
Public Sub AddFieldDef(ByVal lFldDef As clsFieldDef)
On Error Resume Next
mColFields.Add lFldDef, lFldDef.pName
If Err.Number <> 0 Then
MsgBox "Duplicate field name: " & lFldDef.pName, vbExclamation
Err.Clear
End If
On Error GoTo 0
End Sub
' === FACTORY METHOD ===
Public Function CreateFieldDef( _
ByVal lStrName As String, _
ByVal lEnumType As FieldTypes, _
Optional ByVal lBlnIsIndexed As Boolean = False, _
Optional ByVal lBlnIsAutoNumber As Boolean = False, _
Optional ByVal lBlnIsPK As Boolean = False _
) As clsFieldDef
Dim lFld As clsFieldDef
Set lFld = New clsFieldDef
lFld.pName = lStrName
lFld.pType = lEnumType
lFld.pIsIndexed = lBlnIsIndexed
lFld.pIsAutoNumber = lBlnIsAutoNumber
lFld.pIsPK = lBlnIsPK
Set CreateFieldDef = lFld
End Function
' === CREATE THE TABLE ===
Public Sub CreateTable()
Dim lFldDef As clsFieldDef
Dim lFld As DAO.Field
Dim lIdx As DAO.Index
Dim lFldPK As clsFieldDef
Dim lIntPKCount As Integer
' Delete table if exists
On Error Resume Next
mDbDatabase.TableDefs.Delete mStrTableName
Err.Clear
On Error GoTo 0
Set mTdfTableDef = mDbDatabase.CreateTableDef(mStrTableName)
' === Add fields ===
For Each lFldDef In mColFields
' Size for text/binary/varbinary
If lFldDef.pSize > 0 And _
(lFldDef.pType = dbText Or lFldDef.pType = dbBinary Or lFldDef.pType = dbVarBinary) Then
Set lFld = mTdfTableDef.CreateField(lFldDef.pName, lFldDef.pType, lFldDef.pSize)
Else
Set lFld = mTdfTableDef.CreateField(lFldDef.pName, lFldDef.pType)
End If
If lFldDef.pIsAutoNumber Then
lFld.Attributes = dbAutoIncrField
End If
If Not IsNull(lFldDef.pDefaultValue) Then
lFld.DefaultValue = lFldDef.pDefaultValue
End If
lFld.Required = lFldDef.pIsRequired
mTdfTableDef.Fields.Append lFld
Next lFldDef
' === Add Composite Primary Key ===
Set lIdx = mTdfTableDef.CreateIndex("PrimaryKey")
For Each lFldPK In mColFields
If lFldPK.pIsPK Then
lIdx.Fields.Append lIdx.CreateField(lFldPK.pName)
lIntPKCount = lIntPKCount + 1
End If
Next lFldPK
If lIntPKCount > 0 Then
lIdx.Primary = True
mTdfTableDef.Indexes.Append lIdx
End If
' === Add Secondary Indexes ===
For Each lFldDef In mColFields
If lFldDef.pIsIndexed And Not lFldDef.pIsPK Then
Set lIdx = mTdfTableDef.CreateIndex("idx_" & lFldDef.pName)
lIdx.Fields.Append lIdx.CreateField(lFldDef.pName)
mTdfTableDef.Indexes.Append lIdx
End If
Next lFldDef
mDbDatabase.TableDefs.Append mTdfTableDef
mDbDatabase.TableDefs.Refresh
MsgBox "Table '" & mStrTableName & "' created successfully.", vbInformation
End SubAnd yes, this code actually works. Test code:
Last edited:
