Speed up Form

[Amin]

Hi
I have a database based on importing 12 tables monthly by
running macro, 10 out of 12 tables are non indexed tables.
The linked field for all of them is "INCIDENT" field.
Is there a way to index them automatically ? to speed up
the main from with 10 subforms.
Thanks

 

[JohnR]

Call this sub and pass the name of the table
Sub AddIndex(tbl As string)
Dim db As Database
Dim tdf As TableDef
Dim idx As Index
Set db = Currentdb
Set tdf = db.TableDefs(tbl)
Set idx = tdf.CreateIndex("idx_"& tbl)
With idx
.Fields = INCIDENT"
.Primary = True 'To make it a primary key
.Unique = True 'To make it a unique key
End With
tdf.Indexes.Append idx
tdf.Indexes.Refresh
db.TableDefs.Refresh
End Sub

 

[Steve Schapel]

Amin,

An alternative to John's suggestion, is to run an SQL statement using
CREATE INDEX. This has the advantage of being independent of version,
engine, etc, as well as simpler and more efficient. Example syntax
for simple index...
CREATE INDEX index ON table (field)
See Help in Access for more details.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Thanks John and Steve

-----Original Message-----
Amin,

An alternative to John's suggestion, is to run an SQL statement using
CREATE INDEX. This has the advantage of being independent of version,
engine, etc, as well as simpler and more efficient. Example syntax
for simple index...
CREATE INDEX index ON table (field)
See Help in Access for more details.

- Steve Schapel, Microsoft Access MVP




.