Not the right Constraint_Name = szRelationship in Access

[Irena]

Hi all,

Following some help from www.aspfaq.com, I have used this sql command
to find the constraint name of my foreign key:

SELECT
FK_Table = szReferencedObject,
FK_Column = szReferencedColumn,
PK_Table = szObject,
PK_Column = szColumn,
Constraint_Name = szRelationship
FROM
MSysRelationships

Unfortunately, constraint_name = szrelationship does not work; in this
sense: it does not return the displayed column value of my foreign key.
For example, if I have AuthorID (=5) as foreign key and Authors as
Foreign table, I expect that "szRelationship" returned AuthorName
(="Hemingway") but it return something like "reference1" and I don't
know what to do with it.

If there any way of getting the displayed column value of a foreign key
also using ADOX.catalog object or Openschema?
My database is Ms Access of course.

Many thanks,
Irena

[Tom van Stiphout]

On 4 Dec 2004 09:44:13 -0800, "Irena" <ne******@hotmail.com> wrote:

I'm not sure I follow you completely. What's wrong with a Select query
like this:
Select AuthorName from Authors where AuthorID=5

-Tom.

Hi all,

Following some help from www.aspfaq.com, I have used this sql command
to find the constraint name of my foreign key:

SELECT
FK_Table = szReferencedObject,
FK_Column = szReferencedColumn,
PK_Table = szObject,
PK_Column = szColumn,
Constraint_Name = szRelationship
FROM
MSysRelationships

Unfortunately, constraint_name = szrelationship does not work; in this
sense: it does not return the displayed column value of my foreign key.
For example, if I have AuthorID (=5) as foreign key and Authors as
Foreign table, I expect that "szRelationship" returned AuthorName
(="Hemingway") but it return something like "reference1" and I don't
know what to do with it.

If there any way of getting the displayed column value of a foreign key
also using ADOX.catalog object or Openschema?
My database is Ms Access of course.

Many thanks,
Irena

[Irena]

Tom van Stiphout wrote:
Ok, sorry.

I start form the beginning. I want to guess, read the foreign keys in a
generic table. I have an ASP script.

So I can read the keys with ADOX.Catalog, with OpenSchema or with the
select query I was suggested by the site I wrote before.

I know the properties to read the foreign key column name, foreign key
table name, but not the property to read the foreign key display name
(as AuthorName of the example, but it could be something else).

Is there a property that returns me that column?

Here is the code that I use:

For each key in Catalog.Tables(sTAB).Keys
FKtab(i) = key.RelatedTable <-- returns the foreign table

if key.type=2 then ' foreign key
for each colkey in key.Columns
FKcol(i) = colkey.RelatedColumn <- return the foreign column
next
end if
i = i+1
Next
thanks,
cheers
irena