indexes - viewing, creating & dropping problem

[Tcs]

I have a set of twelve (12) tables in my db. After I import my data, I create
an index for the table. (And one I create a second index for.)

During my testing, I may need to re-create an index, so I've added code to
'drop' my index before I try to re-create it. So now I have *another* problem.

If I try to 'drop' the index, but it doesn't exist, Access hiccups. So...

1.) How can I trap for an index that doesn't exist, so I don't try to delete
something that doesn't exist.

I know I can use the 'documenter' in Access to see my indexes...but...is there
another (perhaps 'easier') way I might get something a little more "quick and
dirty"? Something that would only show me the relavent index data?

Thanks in advance,

Tom

 

[Guest]

see VBA help on "Indexes Collection"
You can loop through the existing indexes of a tabledef to see what indexes
are available.

 

[Guest]

I can't say I've ever tried dropping indexes in Access before, but a
technique I use when dropping other objects that may not exist is to disable
error handling temporarily:

Private Sub Foo()
On Error Goto Proc_ERR

'do some stuff

On Error Resume Next
'Drop index
On Error Goto Proc_ERR

'do more stuff

Proc_EXIT:
'Clean up
Exit Sub

Proc_ERR:
'Error handler
Resume Proc_EXIT

HTH,

Jon.

 

[Guest]

Klatuu, how does one loop inside this Collection?

What type of Variable needs to be declared to use in the For-Each loop?
I tried Index, but it wouldn't be accepted.
----------------------
Dim TestInd as ???

For Each TestInd In CurrentDB.TableDefs("MyTable").Indexes
Debug.Print TestInd.Name
Next

Thanks,Alex

 

[Dirk Goldgar]

Klatuu, how does one loop inside this Collection?

What type of Variable needs to be declared to use in the For-Each
loop? I tried Index, but it wouldn't be accepted.
----------------------
Dim TestInd as ???

For Each TestInd In CurrentDB.TableDefs("MyTable").Indexes
Debug.Print TestInd.Name
Next

Thanks,Alex

PMFJI, but you are probably missing the required reference to the
Microsoft DAO 3.6 Object Library. If you add that reference in the
Tools -> References... dialog, a line like "Dim TestInd As Index" should
compile.

Even with that reference set, your code needs a little modification.
Try this version (after setting the reference):

'----- start of code -----
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim TestInd As DAO.Index

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

For Each TestInd In tdf.Indexes
Debug.Print TestInd.Name
Next TestInd

Set tdf = Nothing
Set db = Nothing

'----- end of code -----