could any one teach me how to refrence Indexes table of access db. For example if we have a table player with following fields: pplayerno,name,initial and playerno as primary key. Now i want to know how can i use vba to obtain indexes name of Indexs:players and if it is pimary :no or Unique : yes for each index name in Indexes: players table.Thanks

I am not sure what you are asking.

Do you want to see the properties of the index field, or do you want a list of all of your indexes, or do you want to use the PK to generate a form or report? etc.



mmcdonal

well i want to a vba codes that displays all the indexes avalibale in all tables with information i mentioned in my first post.Thanks

Still totally flummoxed as to why anyone on god's earth would want to do this... but try this:

================================================== ===========
Public Function pfunListIndexes(strTable As String) As String

Dim tblName As DAO.TableDef
Dim cnxdb As DAO.Database
Dim idxName As DAO.Index

Set cnxdb = CurrentDb()
Set tblName = cnxdb.TableDefs(strTable)

For Each idxName In tblName.Indexes
    Debug.Print idxName.Name, idxName.Fields, idxName.Primary, idxName.Unique, idxName.Required
Next idxName

End Function
================================================== ============

Given that this code works, could you reply with a reason why this is useful in your implementation, please.. as I am interested as to why this is valuable to you...

my bad.. i didnt finish the function off, no response string but it displays in the immediate window, so should still work. I still really wanna know what you use it for though.

Thank u for u reply. Well unfortunetly i could not run the above code. I created a new module and pasted your code and when click on run marcros windows cames up and ... . could u tell me how fix this and make it as function rather then printing it in immedite window. Furthermore the above code does not output any thing about if a field is foreign key or alternative key. could u show me how to get thos keys as well.I need these information to make a system cataloge for the access 2000 db!Thank u and lookign forward to your reply.

The reason you can't run it from the macro window is because it's not a macro, it's a function.. ok, not the best made one but a function all the same. You don't run it from the macro window, you use it in code or run it within/from forms, queries, reports etc.

If you are having difficulties with understanding how this code is to be run, then (as previously mentioned) you really need to spend time reading a few books on the subject, I think Wrox might be able to help there? I suggest this just so you might better understand what all this code people keep giving you is for, also when asking people to "debug" stuff for you... it may well be that the thing works perfectly well, you just don't "get" it, like for example: your File Dialog box issue.

Function (and I'll try to make it as straightforward as possible):

================================================== ===========
Public Function pfunListIndexes(strTable As String) As String
Dim tblName As DAO.TableDef
Dim cnxdb As DAO.Database
Dim idxName As DAO.Index
dim strTemp as String

Set cnxdb = CurrentDb()
Set tblName = cnxdb.TableDefs(strTable)

For Each idxName In tblName.Indexes
    strTemp = strTemp & chr$(13) & idxName.Name & ", " & idxName.Fields & ", " & idxName.Primary & ", " & idxName.Unique & ", " & idxName.Required
Next idxName

pfunListIndexes = strTemp

End Function
================================================== =============

There are other properties to the "index" control, type "idxName." somewhere after the FOR statement and watch them come up in your code window. I have given the properties you originally asked for in the code above - you should be able to work out any more you need from here.
I do suggest a better way of handling this data, like maybe writing it to a table, but given you asked for a returned value, it is what you have.

I'm even more curious what you need this for, now! Especially when you dont really know a great deal about Access or VB, but indexes apparently are an absolute must!

Indexes are for tuning up a db or a specific query, I could be wrong! but don't really see any value in being particularly precious about them...

If your database is so data-loaded or bloated that forms/reports/queries will only run with indexes (I'm struggling for reasons to use this!) set up then I think indexes are the least of your worries and getting a SQL box should be your primary concern.

If you feel you need to document them then fair play, myself I would just write them down as you develop. The indexes won't change unless you change them. The documentor, I believe, gives information on indexes too.

Finally... what do you mean by Foreign key, Alternative key? This is for relationships, aint it? While it is vaguely to do with indexes, it is not the same thing at all. What do you mean?