Editing Field Properties
You can also edit field properties on-the-fly (providing that the table is not open). Here are
some examples:
CurrentDb.TableDefs("NewTable").Fields("MemoField").DefaultValue = "abc"
CurrentDb.TableDefs("NewTable").Fields("MemoField").Required = True
CurrentDb.TableDefs("NewTable").Fields("MemoField").AllowZeroLength = False
These VBA statements act on the field MemoField in the table NewTable, and set the
default value to abc, theRequiredproperty to True, and theAllowZeroLengthproperty to
False.
Creating Indexes
You can use VBA to create indexes on tables. These are usually placed on key fields within
a tables so as to speed up the queries on those tables. The downside of this is that the more
indexed fields you have on a table, the longer it takes to insert records because the indexes
must be updated each time.
The easiest way to put an index onto a table is to use a SQL statement:
CurrentDb.Execute "create index NewIndex on NewTable(TextField)"
This creates an index called NewIndex on the table NewTable, specifying the field
YesNoField.
You can delete the index by using:
CurrentDb.Execute ("drop index NewIndex on NewTable")
Use VBA objects to create indexes:Sub CreateIndex()
Dim TDef As TableDef, Idx As Index
Dim db As Database
Set db = CurrentDb()
Set TDef = db.TableDefs("NewTable")
Set Idx = TDef.CreateIndex("MyIndex")
Idx.Fields.Append Idx.CreateField("LongIntegerField")
Idx.Primary = True
Idx.Required = True
Idx.Unique = True
Chapter 31: Create and Change Table Structures 315