Creating Index in Exported Table

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

I am exporting by VBA many queries in one database to another database as
tables. These tables do not appear to inherit the indexes - presumably
because I'm exporting a query to become a table.

I've been trying to figure out how to create an index through VBA or even a
macro.

I'm certain I just need to set the field's "Indexed" property but I can't
find anything in the object browser to use.
 
This example shows how to programmatically create a primary key index, a
single field index, and a multi-field index using the DAO library:

Sub CreateIndexDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("MyTable")

'1. Primary key index.
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ID")
.Primary = True
End With
tdf.Indexes.Append ind

'2. Single-field index.
Set ind = tdf.CreateIndex("MyField")
ind.Fields.Append ind.CreateField("MyField")
tdf.Indexes.Append ind

'3. Multi-field index.
Set ind = tdf.CreateIndex("FullName")
With ind
.Fields.Append .CreateField("Surname")
.Fields.Append .CreateField("FirstName")
End With
tdf.Indexes.Append ind

'Refresh the display of this collection.
tdf.Indexes.Refresh

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub
 
Back
Top