Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    154

    Question Unanswered: Getting a list of indexes

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well - DAO-> TableDef-> Index Collection is one way
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Posts
    154

    Question My thought track

    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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    Sub GetTableIndexes()
        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
                Else
                    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
                        Next fld
                    Next idx
                End If
                Debug.Print "------------------ END INDEXES --------------------" & vbCrLf
            End If
            
        Next TDef
        Set TDef = Nothing
        Set idx = Nothing
        Set fld = Nothing
        
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2002
    Posts
    154

    Cool Thank you

    Very cool pf, I am adapting it to drop the info in a table since the immediate window will only display so much. Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •