by Raghuraja.C (12 Submissions)
Category: Miscellaneous
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sat 14th June 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Easy Code to Create Tables and Fields Dynamically
VBcoders
dbEmployee As Database
Dim TableExists As Boolean
Dim tbDef As TableDef
Dim Fld As Field
'Open the Database file
Set dbEmployee = OpenDatabase("C:\Employee.mdb")
'To Check the table exists on the Database file
TableExists = False
For Each tbDef In dbCompany.TableDefs
If UCase(tbDef.Name) = UCase("EMPLOYEE LIST") Then
TableExists = True
Exit For
End If
Next tbDef
If Not TableExists Then
'To add Table to Database
Set tbDef = dbCompany.CreateTableDef("EMPLOYEE LIST")
Set Fld = tbDef.CreateField("EMPLOYEE NO", dbInteger, 10)
tbDef.Fields.Append Fld
Set Fld = tbDef.CreateField("EMPLOYEE NAME", dbText, 25)
tbDef.Fields.Append Fld
Set Fld = tbDef.CreateField("EMPLOYEE ADDRESS", dbText, 100)
tbDef.Fields.Append Fld
dbCompany.TableDefs.Append tbDef
End If
'To Added Fields to Table
Set tbDef = dbCompany.TableDefs("EMPLOYEE LIST")
NeedItem = True
For Each Fld In tbDef.Fields
If UCase(Fld.Name) = UCase("EMPLOYEE CITY") Then
NeedItem = False
Exit For
End If
Next Fld
If NeedItem Then
Set Fld = tbDef.CreateField("EMPLOYEE CITY", dbText,30)
'To put Default values
Fld.DefaultValue = 'NA'
tbDef.Fields.Append Fld
End If
NeedItem = True
For Each Fld In tbDef.Fields
If UCase(Fld.Name) = UCase("EMPLOYEE STATE") Then
NeedItem = False
Exit For
End If
Next Fld
If NeedItem Then
Set Fld = tbDef.CreateField("EMPLOYEE STATE", dbText,30)
'To put Default values
tbDef.Fields.Append Fld
End If
dbEmployee.Close
Set dbEmployee = Nothing
'To delete the fields and Added
tbDef.Fields.Delete "EMPLOYEE NO"
tbDef.Fields.Delete "EMPLOYEE NAME"
tbDef.Fields.Delete "EMPLOYEE ADDRESS"
tbDef.Fields.Delete "EMPLOYEE CITY"
tbDef.Fields.Delete "EMPLOYEE STATE"
'To Append fields to Table
Set Fld = tbDef.CreateField("EMPLOYEE NO", dbLong, 20)
tbDef.Fields.Append Fld
Set Fld = tbDef.CreateField("EMPLOYEE NAME", dbText, 25)
tbDef.Fields.Append Fld
Set Fld = tbDef.CreateField("EMPLOYEE ADDRESS", dbText, 100)
tbDef.Fields.Append Fld
Set Fld = tbDef.CreateField("EMPLOYEE CITY", dbText,30)
tbDef.Fields.Append Fld
Set Fld = tbDef.CreateField("EMPLOYEE STATE", dbText,30)
tbDef.Fields.Append Fld