Solved

List of Table indexes in MS Access

Posted on 2008-06-19
5
936 Views
Last Modified: 2013-11-27
I would like to create a list of Tables in an Access database along with all of the indexes for each table.  Is there a way to do this?
0
Comment
Question by:dsoderstrom
5 Comments
 
LVL 10

Expert Comment

by:therealmongoose
ID: 21822857
You can do this using the database analyser Tools>Analyse>documenter
0
 
LVL 5

Accepted Solution

by:
Steve Dubyo earned 250 total points
ID: 21822954
The following VBA function will return a list of tables and their indexes..

Function GetTablesAndIndexes() As String
    Dim db As Database
    Dim tds As TableDefs
    Dim td As TableDef
    Dim idx As Index
    Dim s As String
   
    Set db = CurrentDb
    Set tds = db.TableDefs
    For Each td In tds
         s = s & td.Name & vbCr
        For Each idx In td.Indexes
            s = s & "  " + idx.Name & vbCr
        Next
    Next    
    GetTablesAndIndexes = s    
End Function

Which can be used like..

Sub Test()
    Debug.Print GetTablesAndIndexes()
End Sub


0
 
LVL 11

Expert Comment

by:RgGray3
ID: 21823143

Sub IndexObjectX(tblName As String)
 

   Dim dbs As Database

   Dim tdf As TableDef

   Dim idxLoop As Index
 

   Set dbs = CurrentDb()

   Set tdf = dbs(tblName)

   

   With tdf

      ' Create new index, create and append Field

      ' objects to its Fields collection.
 

      Debug.Print .Indexes.Count & " Indexes in " & .Name & " TableDef"
 

      ' Enumerate Indexes collection of Employees

      ' table.

      For Each idxLoop In .Indexes

         Debug.Print "  " & idxLoop.Name

      Next idxLoop
 

   End With

   

   dbs.Close

    Set tdf = Nothing

    Set dbs = Nothing

End Sub

Open in new window

0
 
LVL 11

Expert Comment

by:RgGray3
ID: 21823411
Sorry...  hit send before I was ready
And it looks as if influenz has it covered...  but ...



Sub ListAllTablesAndIndexes()
 

Dim dbs As Database

Dim tdf As TableDef

Dim idxLoop As Index

Dim objTmp As Object
 

On Error GoTo ErrTrap
 

   Set dbs = CurrentDb()

   

    For Each objTmp In dbs.TableDefs

        Set tdf = objTmp

        With tdf

           Debug.Print .Indexes.Count & " Indexes in >>  " & .Name

        

           ' Enumerate Indexes collection of table.

           For Each idxLoop In .Indexes

              Debug.Print "  " & idxLoop.Name

           Next idxLoop

        

        

        End With

ResumeForLoop:

        Debug.Print "---------------------------------------------------"

    Next objTmp

On Error Resume Next

    dbs.Close

    Set tdf = Nothing

    Set dbs = Nothing
 

    Exit Sub
 

ErrTrap:

    Debug.Print "Error in > " & tdf.Name & vbCrLf & Err.Number & " - " & Err.Description

    Resume ResumeForLoop
 

End Sub

Open in new window

0
 

Author Closing Comment

by:dsoderstrom
ID: 31468811
Thanks for the help.  I tweaked your code slightly to get exactly what I wanted.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now