Find Primary Key -Foreign Key relationships in all tables in an Access Database.

Kamal Agnihotri
Kamal Agnihotri used Ask the Experts™
on
Using MS Access Database, I need a list of all Primary Keys and their associated Foreign Keys, from All Tables in an Access Database. Using the query below

SELECT A2.name AS Referenced_Table_Name,
       B2.name AS Referenced_Column_As_FK,
       A1.name AS Referencing_Table_Name,
       B1.name AS Referencing_Column_Name,
S.name AS Constraint_name
FROM  sysforeignkeys fk
INNER JOIN sysobjects A1 ON fk.fkeyid = A1.id
INNER JOIN sysobjects A2 ON fk.rkeyid = A2.id
INNER JOIN syscolumns B1 ON B1.id = A1.id AND B1.colid = fk.fkey
INNER JOIN syscolumns B2 ON B2.id = A2.id AND B2.colid = fk.rkey
INNER JOIN sysobjects S ON fk.constid = S.id
ORDER BY A2.name;

I am getting
Syntax Error (missing operator) in query expression fk.fkeyid = A1.id
INNER JOIN sysobjects A2 ON fk.rkeyid = A2.id
INNER JOIN syscolumns B1 ON B1.id = A1.id AND B1.colid = fk.fkey

-- If the experts have a better query than mine, that lists all Primary Keys and their associated Foreign Keys, from All Tables in an Access Database, that would work as well.

Please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Well, I have not tried that, but the tables in Access are msysObjects not sysObjects  and I'm not aware of a sysColumns table although there is an mSysRelationships.

What I've found is that your best bet is to loop through the tabledefs and then use the relationships to identify the fields associated with each relationship.  Of course that assumes that the database has appropriate relationships defined in a relationship diagram.
Karen FalandaysTraining Specialist

Commented:
What is the purpose of your end result? Why not just use Database Tools>Relationships to view all?
Mark EdwardsChief Technology Officer

Commented:
Dale's on the right track:

Relationships SHOULD be defined in your relationships diagram, which is what Access uses to automatically link tables in queries and  automatically handle other operations as part of its "no-coding" application builder (and it is how they get into the MSysRelationships tables).  However, it is possible to have a bunch of primary and foreign key fields in tables WITHOUT defining any of them in the Relationships window (so nothing in the MSysRelationships table).  You'll also discover that the "automatic handling" of primary/foreign key related processing and other "no-coding" actions that you may have come to expect, don't happen, and you'll have to VBA code all handling of these processes.  (One way or the other, you've got to use someone's code - Microsoft's code or yours.)

If they aren't in the Relationships diagram, Primary Keys can be seen in the design view of tables.  They should also be in the MSys tables where primary keys are defined.

For foreign keys (which can be a lot more difficult to figure out) you could try:
-Looking for field names that are named to indicate they are a foreign key (like "fkFieldName").
-Looking for field names that are the same name as a primary key field name.
-Looking for field names that end in "ID".  Here's a design tip:  In queries, Access automatically tries to link fields which have the same names and end with the letters "ID".  (If you don't believe me... try it).
-Look at how tables are linked in queries.  The links won't be limited to just keys, but you can get an idea of what fields might be foreign keys because they are linked to primary keys and usually don't allow Nulls (field MUST have a value).

Other than that, unless someone else can add more ideas..... you're screwed.....
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Well,
No clue where you found that query, but there are no sysforeignkeys, no sysobjects  and no syscolumns  table within MS Access.
Relationship are stored in the MSysRelationships table, so just look in this table.

After a quick sneak into the table data, the query can be Something like:
SELECT MSysRelationships.szReferencedObject AS FirstTableName,
       MSysRelationships.szReferencedColumn AS PrimaryKey, 
       MSysRelationships.szObject AS SecondTableName, 
       MSysRelationships.szColumn AS ForeignKey
FROM MSysRelationships;

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
There's also some 3rd party tools that do this, like Total Access Analyzer:

http://www.fmsinc.com/MicrosoftAccess/BestPractices.html

Author

Commented:
This question can now be closed.
Mark EdwardsChief Technology Officer

Commented:
Kamal:  Just out of curiosity, how did you get your question solved?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial