Add Index Property To Temp Table

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

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

--
Brendan Reynolds (MVP)


"Larry R Harrison Jr" wrote in message
news:SGzKd.11370$rw.2466@fed1read04...
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 wrote:
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

--
Brendan Reynolds (MVP)

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

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

--
Brendan Reynolds (MVP)


wrote in message
oups.com...

Brendan Reynolds wrote:
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

--
Brendan Reynolds (MVP)

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