Does anyone know of a nice method for enumerating all the indexes and the fields involved and the tables that they are in in Access 2002-2003? I guess what I am looking for is information like the system views in SQL Server or the data dictionary info from Oracle. I'm thinking that there should be some way, using VB probably, to debug.print this list in the immediate window but I am not sure whether DAO, ADODB, or Access Objects, or even a mixture of a couple of these items would do the trick. I thank you all in advance for your time and your responses.
My original thoughts were that to get a list of all the tables to cycle through and look for indexes on, I might have to first use the AllTables collection to get the table names. Then I thought that I'd have to search that table, using Tabledef from DAO maybe, to get any indexes on it. The problem is the help files don't seem to tell me enough about the properties and methods of the Index collection for me to know how to grab the fieldname that is indexed and the index name. I was thinking about something like for each object.name in dbs.AllTables but then I'm not sure what the syntax would be of the statement to check each field in that table to see if it is indexed, or whether the Index collection has information such as table name, field name, index name, thus making the cycling through of AllTables unnecessary. I thank everyone in advance for your time and your responses.
Dim TDef As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Dim i As Integer
For Each TDef In CurrentDb.TableDefs
If Left(TDef.name, 1) <> "~" And Left(TDef.name, 4) <> "msys" Then
Debug.Print "Table name: " & TDef.name
Debug.Print "-------------------- INDEXES ----------------------"
If TDef.Indexes.Count = 0 Then
Debug.Print "NO INDEXES" & vbCrLf
For Each idx In TDef.Indexes
i = 1
Debug.Print vbTab & "Index name: " & idx.name
For Each fld In idx.Fields
Debug.Print vbTab & vbTab & "Field" & i & " name: " & fld.name
i = i + 1
Debug.Print "------------------ END INDEXES --------------------" & vbCrLf
Set TDef = Nothing
Set idx = Nothing
Set fld = Nothing