DAO Topics: Creating a Table

	DAO Topics: Creating a Table

Description

In DAO, a table is an object of type TableDef. The tables of a DAO database are stored in a collection called TableDefs. To give access to this collection, the DAO object is equipped with a property named TableDefs that is of type TableDefs.

To provide access to a TableDef object, the DAO object is equipped with a property called TableDef. Before creating a table, you should first declare a variable of type DAO.TableDef. To initialize the table, use the Set operator to assign the CreateTableDef() method of a database you have previously opened. here is an example:

Private Sub cmdCreateTable_Click()
   Dim dbDeja As DAO.Database
   Dim tblEmployees As DAO.TableDef

   ' Open the database
   Set dbDeja = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbDeja.CreateTableDef("Employees")

   . . .

End Sub

After initializing the table, you can add it to the database by passing it to the Append() method of the TableDefs property of the database that will receive the table. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim dbDeja As DAO.Database
   Dim tblEmployees As DAO.TableDef

   ' Open the database
   Set dbDeja = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbDeja.CreateTableDef("Employees")

   . . .

   ' Add the new table to the database.
   dbDeja.TableDefs.Append tblEmployees
   dbDeja.Close
End Sub

To support fields of a table, the DAO class is equipped with a property named Field, which represents a class of the same name. Therefore, before creating a column in the Microsoft DAO Object Library, declare a variable of type DAO.Field. Here are examples:

Private Sub cmdCreateTable_Click()   
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFirstName As DAO.Field
    Dim fldLastName As DAO.Field
End Sub

Before creating the column(s), first initialize a table as we saw in the previous lesson:

Private Sub cmdCreateTable_Click()
   Dim dbExercise As DAO.Database
   Dim tblEmployees As DAO.TableDef

   ' Open the database
   Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbExercise.CreateTableDef("Employees")

   . . .

End Sub

After initializing the table, you can add the desired columns to it. To create a column, you can call the CreateField() method of the TableDef object and assign it to the column variable. The formula to follow is:

Set fldEmployeeNumber = tblEmployees.CreateField(ByVal ColumnName As String, _
			       			 ByVal DataType As FieldType, _
			       			 ByVal FieldSize As Integer)

The arguments of this method follow exactly the same descriptions we reviewed for the Microsoft Access Object Library.

After creating the column, you can add it to the table. To do this, you can pass it to the Append() method of the Fields collection of the TableDef class. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField(ColumnName, DataType, FieldSize)
    tblEmployees.Fields.Append fldEmployeeNumber
    

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
End Sub

To specify the name of the new column, when calling the DAO.Database.CreateTableDef() method, pass the first argument as a string. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DataType, FieldSize)
    tblEmployees.Fields.Append fldEmployeeNumber
    

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
End Sub

Examples

Here is an example of a simple table that creates one field that will use text of up to 82 characters:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmploymentStatus As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmploymentStatus = tblEmployees.CreateField("EmploymentStatus", DB_TEXT, 82)
    tblEmployees.Fields.Append fldEmploymentStatus

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
End Sub

Here is an example that creates a table named Employees and adds two fields to it:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldFirstName As DAO.Field
    Dim fldLastName As DAO.Field
    Dim fldEmailAddress As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldFirstName = tblEmployees.CreateField("FirstName", DB_TEXT)
    tblEmployees.Fields.Append fldFirstName

    Set fldLastName = tblEmployees.CreateField("LastName", DB_TEXT)
    tblEmployees.Fields.Append fldLastName

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
End Sub

Here is an example that creates a Boolean field:

Private Sub cmdTableCreation_Click()
    Dim curDatabase As DAO.Database
    Dim tblStudents As DAO.TableDef
    Dim colFullName As DAO.Field
    Dim colWasTransfered As DAO.Field

    Set curDatabase = CurrentDb
    
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set colFullName = tblStudents.CreateField("FullName", dbText)
    tblStudents.Fields.Append colFullName
    
    Set colWasTransfered = tblStudents.CreateField("WasTransfered", DB_BOOLEAN)
    tblStudents.Fields.Append colWasTransfered
    
    curDatabase.TableDefs.Append tblStudents
End Sub

Here is an example with integral fields:

Private Sub cmdCreateTable_Click()
    Dim dbCurrent As DAO.Database
    Dim tblCustomers As DAO.TableDef
    Dim fldCustomerName As DAO.Field
    Dim fldCategory As DAO.Field
    Dim fldContractStatus As DAO.Field
    Dim fldContractLength As DAO.Field
    
    ' Specify the database to use
    Set dbCurrent = CurrentDb

    ' Create a new TableDef object.
    Set tblCustomers = dbCurrent.CreateTableDef("Customers")

    Set fldCustomerName = tblCustomers.CreateField("CustomerName", DB_TEXT)
    tblCustomers.Fields.Append fldCustomerName
    
    Set fldCategory = tblCustomers.CreateField("Category", DB_INTEGER)
    tblCustomers.Fields.Append fldCategory
    
    Set fldContractStatus = tblCustomers.CreateField("Contract Status", DB_BYTE)
    tblCustomers.Fields.Append fldContractStatus
    
    Set fldContractLength = tblCustomers.CreateField("Contract Length", DB_LONG)
    tblCustomers.Fields.Append fldContractLength

   ' Add the new table to the database.
   dbCurrent.TableDefs.Append tblCustomers
   dbCurrent.Close
End Sub

Here is an example that creates a table and specifies that the values of a certain field would be required:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldEmployeeName As DAO.Field
    Dim fldEmailAddress As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DB_TEXT)
    fldEmployeeNumber.Required = True
    tblEmployees.Fields.Append fldEmployeeNumber

    Set fldEmployeeName = tblEmployees.CreateField("EmployeeName", DB_TEXT)
    tblEmployees.Fields.Append fldEmployeeName

    Set fldEmailAddress = tblEmployees.CreateField("EmailAddress", DB_TEXT)
    tblEmployees.Fields.Append fldEmailAddress

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
End Sub

Here is an example that specifies that a new field can have a default value:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldEmployeeName As DAO.Field
    Dim fldEmploymentStatus As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DB_TEXT, 10)
    fldEmployeeNumber.Required = True
    tblEmployees.Fields.Append fldEmployeeNumber

    Set fldEmployeeName = tblEmployees.CreateField("EmployeeName", DB_TEXT, 100)
    tblEmployees.Fields.Append fldEmployeeName

    Set fldEmploymentStatus = _
        tblEmployees.CreateField("EmploymentStatus", DB_TEXT, 20)
    fldEmploymentStatus.DefaultValue = "Full Time"
    tblEmployees.Fields.Append fldEmploymentStatus

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
End Sub

Here is an example that creates a primary key:

Private Sub cmdTable_Click()
    Dim curDatabase As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim colEmployeeID As DAO.Field
    Dim colFirstName As DAO.Field
    Dim colLastName As DAO.Field

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colEmployeeID = tblEmployees.CreateField("EmployeeID", DB_LONG, adKeyPrimary)
    colEmployeeID.Attributes = dbAutoIncrField
    tblEmployees.Fields.Append colEmployeeID
    Set colFirstName = tblEmployees.CreateField("FirstName", DB_TEXT)
    tblEmployees.Fields.Append colFirstName
    Set colLastName = tblEmployees.CreateField("LastName", DB_TEXT)
    tblEmployees.Fields.Append colLastName
    
    curDatabase.TableDefs.Append tblEmployees
    MsgBox "A table named Employees has been created"
End Sub