Query function to return table name and primary key name as values

[Phred]

Access 2007, SQL Server 2008. I have normalized a complex (non binary) set of relationships associating multiple tables of people and their relationships to each other. I am past the architecture phase and am testing some of my concepts for functionality. What I need, or at least would be helpful for proofing my concept, would be an Access 2007 Select Query Function (expression builder) that returns the Table Name, and the PrimaryKey Name as values in the returned record set. This will be in the query builder and not in VBA. If there was such a function it would allow a much wider range of records I could join in one table. So I have two questions:

1. Is there a function that will return the table name and primary key name as values in the returned set from the select query.

2. If there is such a function will it survive as a SQL pass through query?

Right now I am not trying to argue the validity of my concept but just the existence of the function.

Any help?

Phred

[drunkenneo]

Hope this is answer to all your question

http://stackoverflow.com/questions/7...e-in-ms-access

[Phred]

Thank you. It is an excellent start. I have read briefly about pulling from the sys tables in Access, but this is the first time I have ever seen any code related to it. I need to pull it apart and see if I can modify it to my needs. I wanted to thank the poster at Stack Overflow but couldn't figure out how on their system. So credit and thanks to Igor Turman, Stack Overflow, for the following example:

SELECT DISTINCT MSysObjects.Name
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId=MSysObjects.Id
WHERE (((MSysQueries.Name1) Like "*" & [TableName] & "*")) OR (((MSysQueries.Name2) Like "*" & [TableName] & "*"))

Thanks to you drunkenneo.

Phred