' Database Table creation using VBA

Sub MakeATable()

Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim idx As Index

 'Start by opening the database
     Set db = CurrentDb()

 'Create a tabledef object
     Set tbl = db.CreateTableDef( "Bottlers")

 'Create a field; set its properties; add it to the tabledef
     Set fld = tbl.CreateField( "BottlerID", dbLong)
    
     fld.OrdinalPosition = 1
     fld.Attributes = dbAutoIncrField
    
     tbl.Fields.Append fld

 'Create another; set its properties; add it to the tabledef
     Set fld = tbl.CreateField( "BottlerName", dbText)
    
     fld.OrdinalPosition = 2
     fld.Size = 50
     fld.Required = True
     fld.AllowZeroLength = False

     tbl.Fields.Append fld

 'Create an index and set its properties
     Set idx = tbl.CreateIndex( "PrimaryKey")

     idx.Primary = True
     idx.Required = True
     idx.Unique = True

 'Add a field to the index
     Set fld = idx.CreateField( "BottlerID")
     idx.Fields.Append fld

 'Add the index to the tabledef
     tbl.Indexes.Append idx

 'Finally add table to the database
     db.TableDefs.Append tbl

 'And refresh the database window
     RefreshDatabaseWindow
    
 'Indicate creation was successful
     MsgBox "The " & tbl.Name & " table was successfully created"

End Sub

Copyright By AccessVision