• Home
 · 
• Miscellaneous
 · 
• Easy Code to Create Tables and Fields Dynamically

Easy Code to Create Tables and Fields Dynamically

Easy Code to Create Tables and Fields Dynamically

Rate Easy Code to Create Tables and Fields Dynamically

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

   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