Access.2007.VBA.Bibl..

.Fields.Append fld

Set fld = .CreateField(Name:=”Department”, _

Type:=dbText, Size:=14)

.Fields.Append fld

Set fld = .CreateField(Name:=”Shift”, _

Type:=dbText, Size:=20)

.Fields.Append fld

Set fld = .CreateField(Name:=”AnnualBonus”, _

Type:=dbCurrency)

fld.DefaultValue = 500

.Fields.Append fld

Set fld = .CreateField(Name:=”ShiftSupervisor”, _

Type:=dbBoolean)

fld.DefaultValue = False

.Fields.Append fld

End With

Add the new table to the TableDefs collection.

.TableDefs.Append Object:=tdfNew

End With

List the TableDefs in the database after appending the new table.

Debug.Print “TableDefs in “ & dbsNorthwind.Name

For Each tdf In dbsNorthwind.TableDefs

Debug.Print vbTab & tdf.Name

Next tdf

dbsNorthwind.Close

ErrorHandlerExit:

Exit Sub

ErrorHandler:

If Err.Number = 3010 Then

strPrompt = “Table name already used; “ _

& “please enter another name”

strTitle = “Duplicate table name”

MsgBox prompt:=strPrompt, _

Buttons:=vbExclamation + vbOKOnly, Title:=strTitle

GoTo NameNewTable

Else

MsgBox “Error No: “ & Err.Number & “; Description: “ _

& Err.Description

Resume ErrorHandlerExit

End If

End Sub

Part II Writing VBA Code to Exchange Data between Office Components