A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Indexes, Primary Keys and Relationships in MS Access



 
 
Thread Tools Display Modes
  #1  
Old August 12th, 2004, 05:31 PM
Naveen
external usenet poster
 
Posts: n/a
Default Indexes, Primary Keys and Relationships in MS Access

Hi,

How can i query/get the following in an MS Access 2002
Database: -

1. List of Indexed Fields in the MDB
2. All the Primary Key Fields in the MDB
3. List of Constraints/Foreign Key relationships in the MDB

Is there some way to query this information instead of
going table by table in the design view?

Thanks!
  #2  
Old August 12th, 2004, 05:54 PM
Allen Browne
external usenet poster
 
Posts: n/a
Default Indexes, Primary Keys and Relationships in MS Access

You can access these things programmatically.

This example lists the names of the relationships in the current database,
including the table and foreign table. Each relation has other properties as
well.

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

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable
Next

Set rel = 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.

"Naveen" wrote in message
...

How can i query/get the following in an MS Access 2002
Database: -

1. List of Indexed Fields in the MDB
2. All the Primary Key Fields in the MDB
3. List of Constraints/Foreign Key relationships in the MDB

Is there some way to query this information instead of
going table by table in the design view?

Thanks!



  #3  
Old August 12th, 2004, 06:07 PM
Naveen
external usenet poster
 
Posts: n/a
Default Indexes, Primary Keys and Relationships in MS Access

Thanks! This works.

I was curious to know if there was some way to query the
MDB and get the information instead of coding.

Thank you,


-----Original Message-----
You can access these things programmatically.

This example lists the names of the relationships in the

current database,
including the table and foreign table. Each relation has

other properties as
well.

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

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable
Next

Set rel = 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.

"Naveen" wrote

in message
...

How can i query/get the following in an MS Access 2002
Database: -

1. List of Indexed Fields in the MDB
2. All the Primary Key Fields in the MDB
3. List of Constraints/Foreign Key relationships in the

MDB

Is there some way to query this information instead of
going table by table in the design view?

Thanks!



.

  #4  
Old August 12th, 2004, 06:15 PM
Allen Browne
external usenet poster
 
Posts: n/a
Default Indexes, Primary Keys and Relationships in MS Access

This is undocumented, but may help:

SELECT MSysRelationships.* FROM MSysRelationships;

--
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.

"Naveen" wrote in message
...
Thanks! This works.

I was curious to know if there was some way to query the
MDB and get the information instead of coding.

Thank you



  #5  
Old August 12th, 2004, 07:17 PM
Naveen
external usenet poster
 
Posts: n/a
Default Indexes, Primary Keys and Relationships in MS Access

Thanks!
-----Original Message-----
This is undocumented, but may help:

SELECT MSysRelationships.* FROM MSysRelationships;

--
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.

"Naveen" wrote in

message
...
Thanks! This works.

I was curious to know if there was some way to query the
MDB and get the information instead of coding.

Thank you



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
More assistance with Many to Many Relationships Lynn Database Design 3 July 9th, 2004 04:22 PM
Many to Many Relationships Lynn Database Design 7 July 9th, 2004 03:08 PM
Access 2002 - Many to Many Relationships Lynn General Discussion 5 July 8th, 2004 12:55 AM
Establishing Relationships with ID numbers as foreign keys lybargera Database Design 2 June 16th, 2004 07:30 PM
Primary Keys and Relationships TR Database Design 2 May 23rd, 2004 03:19 AM


All times are GMT +1. The time now is 11:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.