Append and Delete Methods Example

This example uses either the Append method or the Delete method to modify the Fields collection of a TableDef. The AppendDeleteField procedure is required for this procedure to run.

Sub AppendX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim fldLoop As Field

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind.TableDefs!Employees

    ' Add three new fields.
    AppendDeleteField tdfEmployees, "APPEND", _
        "E-mail", dbText, 50
    AppendDeleteField tdfEmployees, "APPEND", _
        "Http", dbText, 80
    AppendDeleteField tdfEmployees, "APPEND", _
        "Quota", dbInteger, 5

    Debug.Print "Fields after Append"
    Debug.Print , "Type", "Size", "Name"

    ' Enumerate the Fields collection to show the new fields.
    For Each fldLoop In tdfEmployees.Fields
        Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
    Next fldLoop

    ' Delete the newly added fields.
    AppendDeleteField tdfEmployees, "DELETE", "E-mail"
    AppendDeleteField tdfEmployees, "DELETE", "Http"
    AppendDeleteField tdfEmployees, "DELETE", "Quota"

    Debug.Print "Fields after Delete"
    Debug.Print , "Type", "Size", "Name"

    ' Enumerate the Fields collection to show that the new 
    ' fields have been deleted.
    For Each fldLoop In tdfEmployees.Fields
        Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
    Next fldLoop

    dbsNorthwind.Close

End Sub

Sub AppendDeleteField(tdfTemp As TableDef, _
    strCommand As String, strName As String, _
    Optional varType, Optional varSize)

    With tdfTemp

        ' Check first to see if the TableDef object is 
        ' updatable. If it isn't, control is passed back to 
        ' the calling procedure.
        If .Updatable = False Then
            MsgBox "TableDef not Updatable! " & _
                "Unable to complete task."
            Exit Sub
        End If

        ' Depending on the passed data, append or delete a
        ' field to the Fields collection of the specified
        ' TableDef object.
        If strCommand = "APPEND" Then
            .Fields.Append .CreateField(strName, _
                varType, varSize)
        Else
            If strCommand = "DELETE" Then .Fields.Delete _
                strName
        End If

    End With

End Sub