Add Index Property To Temp Table

[Larry R Harrison Jr]

I have Access 97. I have a "tmp" table that is created in code via a query
(or SQL statements), I then need to modify 2 of the fields to be index (dups
okay). I cannot for the life of me figure out how to do this in code.

The field is "Approved", it's a YES/NO field, the table is "tmpTable."

How do I do this?

LRH

 

[Brendan Reynolds]

The commented out lines are the lines I used to create the table. You don't
need them because, of course, the table already exists in your database ...

Public Sub AddIndex()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set db = CurrentDb
'Set tdf = db.CreateTableDef("tmpTable")
Set tdf = db.TableDefs("tmpTable")
'Set fld = tdf.CreateField("Approved", dbBoolean)
'tdf.Fields.Append fld
'db.TableDefs.Append tdf
Set idx = tdf.CreateIndex("Approved")
Set fld = idx.CreateField("Approved", dbBoolean)
idx.Fields.Append fld
tdf.Indexes.Append idx

End Sub

 

[larrytucaz]

The commented out lines are the lines I used to create the table. You don't
need them because, of course, the table already exists in your database ...

Public Sub AddIndex()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set db = CurrentDb
'Set tdf = db.CreateTableDef("tmpTable")
Set tdf = db.TableDefs("tmpTable")
'Set fld = tdf.CreateField("Approved", dbBoolean)
'tdf.Fields.Append fld
'db.TableDefs.Append tdf
Set idx = tdf.CreateIndex("Approved")
Set fld = idx.CreateField("Approved", dbBoolean)
idx.Fields.Append fld
tdf.Indexes.Append idx

End Sub

Thanks for the code. I did fortunately find some earlier in Google
archives. The part that throws/confuses me is the Set fld=.createfield
(etc) because the field in question already exists.

LRH

 

[Brendan Reynolds]

Well, it already exists in the table, but not in the index.