Refreshing Indexes

[Brett]

Hello,

CreateNewIndex creates and sets an index to !MYTable.
However, !MYTable is deleted and recreated without the
indexes and I would like to recall CreateNewIndex to reset
the indexes. When I call CreateNewIndex again...it lets
me know the index already exist.

Is there an easy way to recall the DepartmentIndex

Sub CreateNewIndex()

Dim dbs As Database, tdf As TableDef, idx As Index
Dim fld1 As Field

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to Employees table.
Set tdf = dbs.TableDefs!MYTable
Set idx = tdf.CreateIndex("DepartmentIndex")
Set fld1 = idx.CreateField("Department")

idx.Fields.Append fld1

idx.Required = True
tdf.Indexes.Append

Set dbs = Nothing

End Sub

 

[TC]

If you delete a table, the indexes will be deleted as well. So you should
definitely be able to call your sub again, to recreate the table.

Try this:

(1) Delete the table;
(2) Close & reopen the db;
(3) Check that the table definition has disappeared;
(4) Create the table without the index;
(5) Close & reopen the db;
(6) Check that you can see the table, but not the index (via the user
interface - not using code);
(7) Re-run your sub - I bet it will work.

HTH,
TC

 

[Brett]

Thanks for the response TC,

Maybe I mis-spoke, I meant that the code below

Set idx = tdf.CreateIndex("DepartmentIndex")

Lets me know that I cannot create an index named
("DepartmentIndex") due to the fact that it already exist.

Thanks
Brent

 

[TC]

Brett, I don't understand. If you delete the table & recreate it without any
indexes, you should then be able to run your code to create the department
index.

If you say that this does *not* work, then, you need to try the 7 steps that
I suggested, & tell me what happens.

HTH,
TC