Where are Indexes and Relationships stored, within Access MDB file?

[tekanet]

Hello folks,

I'm developing a program that reads an access MDB file and produce
some scripts to rebuild the same structure against other databases
(MSSQL, MySQL and so on).

Reading structure of tables and fields was quite simple. Now I'm stuck
because I can't find where to read to access infos about indexes and
relationships. Probably for relationships I've to read the hidden
table MSysRelationshi ps (I didn't do that 'till now), but I haven't
found anything about indexes.

Any suggestion?

TIA, tK

[Allen Browne]

Check the Relations collection:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTabl e
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function
Indexes belong to a TableDef:

Function ShowIndexes(str Table As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(st rTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IIf(ind.Primary , "Primary", ""), _
IIf(ind.Foreign , "Foreign", ""), ind.Fields.Coun t
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"tekanet" <te*****@inwind .it> wrote in message
news:81******** *************** ***@posting.goo gle.com...

I'm developing a program that reads an access MDB file and produce
some scripts to rebuild the same structure against other databases
(MSSQL, MySQL and so on).

Reading structure of tables and fields was quite simple. Now I'm stuck
because I can't find where to read to access infos about indexes and
relationships. Probably for relationships I've to read the hidden
table MSysRelationshi ps (I didn't do that 'till now), but I haven't
found anything about indexes.

Any suggestion?

TIA, tK

[Pieter Linden]

te*****@inwind. it (tekanet) wrote in message news:<81******* *************** ****@posting.go ogle.com>...

Hello folks,

I'm developing a program that reads an access MDB file and produce
some scripts to rebuild the same structure against other databases
(MSSQL, MySQL and so on).

Reading structure of tables and fields was quite simple. Now I'm stuck
because I can't find where to read to access infos about indexes and
relationships. Probably for relationships I've to read the hidden
table MSysRelationshi ps (I didn't do that 'till now), but I haven't
found anything about indexes.

Any suggestion?

TIA, tK

If you're good enough at programming to be digging around the guts of
Access, you should probably own the Access Developer's Handbook by
Getz, Litwin, Gilbert/Gunderloy. there's code in there that does that
kind of thing - free with the book and very well worth the price of
the book.