MAOL: Creating a Table

	MAOL: Creating a Table

Description

To programmatically create a table using the Microsoft Access Object Library, you can first declare a variable of type Object and then initialize it with the CreateTableDef() method of the current database object. This method takes as argument the name of the new table. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
End Sub

After creating the table, you must add it to the current database. To support this, the CurrentDb object is equipped with the TableDefs property. TableDefs is in fact a collection. The TableDefs collection is equipped with the Append() method that is used to add a new table to the current database. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    . . .
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
    
    . . . Use the table here

    Set tblStudents = Nothing
End Sub

To support the creation of a column using the Microsoft Access Object Library, its table is equipped with a method named CreateField. This method takes three arguments and its syntax is:

Set field = object.CreateField(ByValye ColumnName As String, _
			       ByVal DataType As FieldType, _
			       ByVal Size As Integer)

Before calling this method, declare a variable of type Object for the new column. When calling this method, get a reference to it and assign it to the variable you would have created for the column. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField(ColumnName, DataType, Size)
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

None of the arguments to the CreateField() method is required.

The first argument of the CreateField() method, name, soecifies the name of the new column. The name follows the rules we saw for names of columns. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", DataType, Size)
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

If you call the method without specifying the name of the column, at one time or another before actually creating the field, you will have to set its name. Otherwise you will receive an error. to support this, the field object has a property named Name. Therefore, to specify the name of the column, access its Name property and assign the desired string. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students1")
    
    Set fldFullName = tblStudents.CreateField
    fldFullName.Name = "FullName"
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

The second argument to the CreateField() method specifies the data type that would be applied on the column. We will review data types in other sections. Once again, the argument is optional. If you omit it when calling the method, you must specify it before actually creating the table. To support this, the field class is equipped with a property named Type. Therefore, to specify the data type of a column, assign the desired type to its Type property.

The third argument to the CreateField() method holds a piece of information that has to do with either the computer memory or the number of characters.

After creating a column, you must add it to the table. To support this, the table object is equipped with a property named Fields, which is a collection. We saw that the collections classes are equipped with a method named Add. The Fields collection of the table is equipped with an equivalent method but named Append method. This method takes as argument an object that represents the collection.

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", DataType, Size)
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

Examples

Here is an example that creates a table with two fields:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFirstName As Object, fldLastName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFirstName = tblStudents.CreateField("FirstName", dbText)
    tblStudents.Fields.Append fldFirstName
    
    Set fldLastName = tblStudents.CreateField("LastName", dbText)
    tblStudents.Fields.Append fldLastName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

Here is an example that creates a Boolean field:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colIsMarried As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append colFullName
    Set colIsMarried = tblEmployees.CreateField("IsMarried", dbBoolean)
    tblEmployees.Fields.Append colIsMarried
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblEmployees
End Sub

You can also use the DB_BOOLEAN type.

Here is an example of a table with the natural number types:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colEmployeeNumber As Object
    Dim colFullName As Object
    Dim colExemptions As Object
    Dim colMaritalStatus As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colEmployeeNumber = tblEmployees.CreateField("[Employee Number]", bdLong)
    tblEmployees.Fields.Append colEmployeeNumber
    Set colFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append colFullName
    Set colMaritalStatus = tblEmployees.CreateField("MaritalStatus", dbByte)
    tblEmployees.Fields.Append colMaritalStatus
    Set colExemptions = tblEmployees.CreateField("Exemptions", dbInteger)
    tblEmployees.Fields.Append colExemptions
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

Here is an example that specifies that the values of a column would be required:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFirstName As Object, fldLastName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFirstName = tblStudents.CreateField("FirstName", dbText)
    tblStudents.Fields.Append fldFirstName
    
    Set fldLastName = tblStudents.CreateField("LastName", dbText)
    fldLastName.Required = True
    tblStudents.Fields.Append fldLastName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

Here is an example that creates a table and specifies that a certain field can use a default value:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object
    Dim fldGender As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("FullName", dbText, 120)
    tblStudents.Fields.Append fldFullName
    
    Set fldGender = tblStudents.CreateField("Gender", dbText, 20)
    fldGender.DefaultValue = "Female"
    tblStudents.Fields.Append fldGender
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

Here is an example that creates a primary key:

Private Sub cmdTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colEmployeeID As Object
    Dim colFirstName As Object
    Dim colLastName As Object

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

Here is another example:

Private Sub cmdTable_Click()
    Dim dbCurrent As Object
    Dim tblEmployees As Object
    Dim fldEmployee As Object
    
    Set dbCurrent = CurrentDb
    
    Set tblEmployees = dbCurrent.CreateTableDef("Employees")
    
    Set fldEmployee = tblEmployees.CreateField("DateHired", dbDate)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("FirstName", dbText, 40)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("LastName", dbText, 40)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("Department", dbText, 50)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("HourlySalary", dbDouble)
    tblEmployees.Fields.Append fldEmployee
    
    dbCurrent.TableDefs.Append tblEmployees
    MsgBox "A table named Employees has been created"
    
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('10/22/2006', 'Jerry', 'Carlton', " & _
                 "'Corporate', 22.45);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('05/12/2000', 'Christopher', 'Haloux', " & _
                 "'Accounting', 28.05);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('12/05/2005', 'Grattie', 'Sanion', " & _
                 "'Human Resources', 18.45);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('08/08/2002', 'Nicolas', 'Sands', 'IT', 19.85);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('12/04/1998', 'Dennis', 'Fleming', " & _
                 "'Corporate', 14.55);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('06/10/2008', 'Julie', 'Woods', " & _
                 "'Corporate', 30.2);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('12/05/2005', 'Barbara', 'Seans', " & _
                 "'Human Resources', 18.45);"
End Sub