By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,332 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,332 IT Pros & Developers. It's quick & easy.

View all indexes in Access table

P: 79
Hi!
What query should I use to get all the indexes defined on a specific table? Even the ones which are defined on multiple columns.
Thanks.
May 22 '07 #1
Share this Question
Share on Google+
6 Replies

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi!
What query should I use to get all the indexes defined on a specific table? Even the ones which are defined on multiple columns.
Thanks.
I'm not sure what you mean by a query. You can find the indexes on any table by opening the table in design view and clicking indexes on the view menu.
May 25 '07 #2

ADezii
Expert 5K+
P: 8,750
Hi!
What query should I use to get all the indexes defined on a specific table? Even the ones which are defined on multiple columns.
Thanks.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim tdf As DAO.TableDef
  3. Dim idx As DAO.Index
  4. Dim fld As DAO.Field
  5.  
  6. Dim strField As String
  7.  
  8. Set db = CurrentDb()
  9. Set tdf = db.TableDefs("tblEmployee")
  10. Set rst = db.OpenRecordset("tblEmployee", dbOpenTable)
  11.  
  12. ' List values for each index in the collection.
  13. For Each idx In tdf.Indexes
  14.   ' The index object contains a collection of fields,
  15.   ' one for each field the index contains.
  16.   Debug.Print
  17.   Debug.Print "Index: " & idx.Name
  18.   Debug.Print "==========================="
  19.  
  20.     For Each fld In idx.Fields
  21.       Debug.Print "Field Name: " & fld.Name
  22.     Next fld
  23.     Debug.Print
  24. Next idx
May 27 '07 #3

JConsulting
Expert 100+
P: 603
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim tdf As DAO.TableDef
  3. Dim idx As DAO.Index
  4. Dim fld As DAO.Field
  5.  
  6. Dim strField As String
  7.  
  8. Set db = CurrentDb()
  9. Set tdf = db.TableDefs("tblEmployee")
  10. Set rst = db.OpenRecordset("tblEmployee", dbOpenTable)
  11.  
  12. ' List values for each index in the collection.
  13. For Each idx In tdf.Indexes
  14.   ' The index object contains a collection of fields,
  15.   ' one for each field the index contains.
  16.   Debug.Print
  17.   Debug.Print "Index: " & idx.Name
  18.   Debug.Print "==========================="
  19.  
  20.     For Each fld In idx.Fields
  21.       Debug.Print "Field Name: " & fld.Name
  22.     Next fld
  23.     Debug.Print
  24. Next idx
Hey ADezii, is the Recordset part there in case you want to output to a table?
J
May 27 '07 #4

ADezii
Expert 5K+
P: 8,750
Hey ADezii, is the Recordset part there in case you want to output to a table?
J
No, It's a lot simpler than that - code line that is not required (LOL). Thanks for the catch.
May 27 '07 #5

JConsulting
Expert 100+
P: 603
No, It's a lot simpler than that - code line that is not required (LOL). Thanks for the catch.
shhh, I won't tell...I actually stole your small routine. I have a use for it myself! Good work btw. Thx.
J
May 27 '07 #6

ADezii
Expert 5K+
P: 8,750
shhh, I won't tell...I actually stole your small routine. I have a use for it myself! Good work btw. Thx.
J
Anything that you can possibly find of interest that I post, please feel free to help yourself to. At least I now know that 'ALL' my work is not in vain! BTW, thanks for the compliment.
May 27 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.