Solved

List of Table indexes in MS Access

Posted on 2008-06-19
5
939 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

813 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

17 Experts available now in Live!

Get 1:1 Help Now