Microsoft Access VBA Macro Programming

MyTable.Fields.Append MyTable.CreateField("IntegerField", dbInteger)

MyTable.Fields.Append MyTable.CreateField("SingleNumberField", dbSingle)

MyTable.Fields.Append MyTable.CreateField("DoubleNumberField", dbDouble)

CurrentDb.TableDefs.Append MyTable

Application.RefreshDatabaseWindow

End Sub

This code uses theDimstatement to create an object to hold the Table Definition. It then

sets this object to a newly created table definition called NewTable. New fields are then

created in the table by using theAppendmethod of theFieldscollection for the new table

definition.

The field is created using theCreateFieldmethod and then appended to the Fields

collection. The create field method requires a field name (e.g., TextField) and a field type

parameter (e.g., dbText). Notice that it is only the text field that uses the optional size

parameter. If this is omitted, then the size is set to the default of 255 characters.

Finally, the newTableDefobject is appended to theTableDefscollection and the database

window is refreshed so you can see your new table in the Navigation pane.

If you run this code and then open the table NewTable in Design mode, you will see all

the fields of different type that have been created.

Deleting a Table or a Field

You can easily delete a table (providing it is not in use) by using:

CurrentDb.TableDefs.Delete "NewTable"

To delete a single field in the table, use:

CurrentDb.TableDefs("NewTable").Fields.Delete "TextField"

This deletes the field TextField in the table NewTable and deletes all the data in that field

at the same time.

No warning messages are given for either of these methods, so beware. You could easily

lose much data.

314 Microsoft Access 2010 VBA Macro Programming