DZ said:
Is there a way to add an index of the type, Yes (Duplicates OK) to all
fields in a table using VBA code, prefereable using DAO. The table currently
has to indexes.
It takes time to manage indexes, especially when
adding/appending new records. You rarely really want an
index on every field, especially if the table is relatively
large so don't go overboard. Generally, I skip indexing a
field unless its used in a Join clause. When you can
anticipate a query has a performance issue (or just stumble
into one), add indexes for the fields used in simple
conditions in the Where clause,
Also remember that you can have a maximum of 32 indexes in
an A2003 or earlier table (I don't know about A2007). This
includes the invisible indexes that are automatically
created for every relationship with referential integrity
enforced.
Do not create duplicate indexes, they are a complete waste
of time and resources.
Anyway, here's some air code to give you the general idea:
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim ndx As Index
Set db = CurrentDb()
Set tdf = db.TableDefs("your table name")
For Each fld In tdf
Set ndx = tdf.CreateIndex("ndx" & fld.Name)
ndx.Fields.Append ndx.CreateField(fld.Name)
tdf.Append ndx
Next fld
tdf.Indexes.Refresh
Set ndx = Nothing
Set tdf = Nothing
Set db = Nothing
Be sure to do your testing of this kind of thing on a copy
of a test table so you can quickly get back to your orifinal
starting point.
