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