Retrieving Access foreign key info via ADOX

Retrieving Access foreign key info via ADOX

Post by ajm.. » Wed, 01 Dec 1999 04:00:00



I am using the latest MDAC 2.1 SP2 and the OLEDB provider for Jet 4.0.
Using ADOX, I can access a wide range of metadata from an Access
database. However, when it comes to foreign keys, I can't see how to
retrieve one vital piece of information.

If you have two tables, fktab1 and fktab2, and assume that fktab1 has
two columns, t1c1 and t1c2, and fktab2 has two columns, t2c1 and t2c2.

Then suppose we have a unique index on fktab1(t1c1,t1c2) and then
create a foreign key as follows

ALTER TABLE fktab2 ADD CONSTRAINT fk1 FOREIGN KEY (t2c1,t2c2)
REFERENCES fktab1 (t1c1,t1c2)

Now using ADOX I can discover that fk1 is a member of the keys
collection for fktab2. I cannot iterate over the columns collection of
that key - this seems to be a bug with ADOX, but since there always
appears to be an index entry created with the same name as the key, I
*can* iterate over the columns collection for that index on fktab2.
This gives me the columns that fk1 covers on fktab2.

Also I can find that the foreign key references fktab1 using
the 'relatedtable' property of the key. This returns the table name as
a string.

But what I *cannot* find out is the 'references' column coverage on
fktab1. In other words, what constraint (and hence its column coverage)
was referred to in the REFERENCES clause.

In the example, this is t1c1 and t1c2. I cannot assume it is the
primary key for the table; you can create a unique key on fktab1 and
reference that as a foreign key. So how do I get this information?.
Certainly there doesn't seem to be anything stored against either
fktab1 or fktab2 in the ADOX model that I can find, even spelunking
over the properties collection revealed nothing.

Incidentally, where on earth is Access storing this metadata. I can't
find it in MSysObjects and although some articles in the KB refer to
MSysIndexes, this table doesn't seem to exist. In any case, querying
MSysObjects through ODBC is refused with a permissions error, even when
connecting as Admin. Does anyone know how to get metadata out of Access
other than using ADOX, convenient though it undoubtedly is?.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Retrieving Access foreign key info via ADOX

Post by mary chipma » Wed, 01 Dec 1999 04:00:00


getting metadata out of Access is best accomplished with DAO. check
out the Index and Relation objects in DAO help. there's a bunch of
intrinsic constants that map to their various attributes and
properties that you can retrieve.

>I am using the latest MDAC 2.1 SP2 and the OLEDB provider for Jet 4.0.
>Using ADOX, I can access a wide range of metadata from an Access
>database. However, when it comes to foreign keys, I can't see how to
>retrieve one vital piece of information.

>If you have two tables, fktab1 and fktab2, and assume that fktab1 has
>two columns, t1c1 and t1c2, and fktab2 has two columns, t2c1 and t2c2.

>Then suppose we have a unique index on fktab1(t1c1,t1c2) and then
>create a foreign key as follows

>ALTER TABLE fktab2 ADD CONSTRAINT fk1 FOREIGN KEY (t2c1,t2c2)
>REFERENCES fktab1 (t1c1,t1c2)

>Now using ADOX I can discover that fk1 is a member of the keys
>collection for fktab2. I cannot iterate over the columns collection of
>that key - this seems to be a bug with ADOX, but since there always
>appears to be an index entry created with the same name as the key, I
>*can* iterate over the columns collection for that index on fktab2.
>This gives me the columns that fk1 covers on fktab2.

>Also I can find that the foreign key references fktab1 using
>the 'relatedtable' property of the key. This returns the table name as
>a string.

>But what I *cannot* find out is the 'references' column coverage on
>fktab1. In other words, what constraint (and hence its column coverage)
>was referred to in the REFERENCES clause.

>In the example, this is t1c1 and t1c2. I cannot assume it is the
>primary key for the table; you can create a unique key on fktab1 and
>reference that as a foreign key. So how do I get this information?.
>Certainly there doesn't seem to be anything stored against either
>fktab1 or fktab2 in the ADOX model that I can find, even spelunking
>over the properties collection revealed nothing.

>Incidentally, where on earth is Access storing this metadata. I can't
>find it in MSysObjects and although some articles in the KB refer to
>MSysIndexes, this table doesn't seem to exist. In any case, querying
>MSysObjects through ODBC is refused with a permissions error, even when
>connecting as Admin. Does anyone know how to get metadata out of Access
>other than using ADOX, convenient though it undoubtedly is?.

>Sent via Deja.com http://www.deja.com/
>Before you buy.


 
 
 

Retrieving Access foreign key info via ADOX

Post by Stefan Zschock » Wed, 01 Dec 1999 04:00:00


You can use Schema-rowsets through the Connection.OpenSchema method. This
gives you everything you need and also is supported by more providers than
ADOX.
Stefan

> I am using the latest MDAC 2.1 SP2 and the OLEDB provider for Jet 4.0.
> Using ADOX, I can access a wide range of metadata from an Access
> database. However, when it comes to foreign keys, I can't see how to
> retrieve one vital piece of information.

> If you have two tables, fktab1 and fktab2, and assume that fktab1 has
> two columns, t1c1 and t1c2, and fktab2 has two columns, t2c1 and t2c2.

> Then suppose we have a unique index on fktab1(t1c1,t1c2) and then
> create a foreign key as follows

> ALTER TABLE fktab2 ADD CONSTRAINT fk1 FOREIGN KEY (t2c1,t2c2)
> REFERENCES fktab1 (t1c1,t1c2)

> Now using ADOX I can discover that fk1 is a member of the keys
> collection for fktab2. I cannot iterate over the columns collection of
> that key - this seems to be a bug with ADOX, but since there always
> appears to be an index entry created with the same name as the key, I
> *can* iterate over the columns collection for that index on fktab2.
> This gives me the columns that fk1 covers on fktab2.

> Also I can find that the foreign key references fktab1 using
> the 'relatedtable' property of the key. This returns the table name as
> a string.

> But what I *cannot* find out is the 'references' column coverage on
> fktab1. In other words, what constraint (and hence its column coverage)
> was referred to in the REFERENCES clause.

> In the example, this is t1c1 and t1c2. I cannot assume it is the
> primary key for the table; you can create a unique key on fktab1 and
> reference that as a foreign key. So how do I get this information?.
> Certainly there doesn't seem to be anything stored against either
> fktab1 or fktab2 in the ADOX model that I can find, even spelunking
> over the properties collection revealed nothing.

> Incidentally, where on earth is Access storing this metadata. I can't
> find it in MSysObjects and although some articles in the KB refer to
> MSysIndexes, this table doesn't seem to exist. In any case, querying
> MSysObjects through ODBC is refused with a permissions error, even when
> connecting as Admin. Does anyone know how to get metadata out of Access
> other than using ADOX, convenient though it undoubtedly is?.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

Retrieving Access foreign key info via ADOX

Post by Conor Cunningha » Wed, 01 Dec 1999 04:00:00



> I am using the latest MDAC 2.1 SP2 and the OLEDB provider for Jet 4.0.
> Using ADOX, I can access a wide range of metadata from an Access
> database. However, when it comes to foreign keys, I can't see how to
> retrieve one vital piece of information.

> If you have two tables, fktab1 and fktab2, and assume that fktab1 has
> two columns, t1c1 and t1c2, and fktab2 has two columns, t2c1 and t2c2.

> Then suppose we have a unique index on fktab1(t1c1,t1c2) and then
> create a foreign key as follows

> ALTER TABLE fktab2 ADD CONSTRAINT fk1 FOREIGN KEY (t2c1,t2c2)
> REFERENCES fktab1 (t1c1,t1c2)

> Now using ADOX I can discover that fk1 is a member of the keys
> collection for fktab2. I cannot iterate over the columns collection of
> that key - this seems to be a bug with ADOX, but since there always
> appears to be an index entry created with the same name as the key, I
> *can* iterate over the columns collection for that index on fktab2.
> This gives me the columns that fk1 covers on fktab2.

> Also I can find that the foreign key references fktab1 using
> the 'relatedtable' property of the key. This returns the table name as
> a string.

> But what I *cannot* find out is the 'references' column coverage on
> fktab1. In other words, what constraint (and hence its column coverage)
> was referred to in the REFERENCES clause.

It should be the primary key on the "referenced" table in all cases.  It
might be
possible to reference a UNIQUE key, but you're not supposed to do so, per
SQL-92 rules.

Keep in mind that the Jet engine, while a lot closer to SQL-92 rules in the
4.0 release, has
some legacy features that precluded complete conformance.. OLE DB/ADOX are
built on the
SQL-92 rules, so some things may not entirely match.

I don't know off-hand why the ADOX keys collection is not working for you,
but I do
know that it is built on the schema rowsets in the OLE DB Provider.. You can
access
these in ADO through cn.openschema.. You might look there to find additional
information if
the ADOX code does not work 100% properly.

Quote:> In the example, this is t1c1 and t1c2. I cannot assume it is the
> primary key for the table; you can create a unique key on fktab1 and
> reference that as a foreign key. So how do I get this information?.
> Certainly there doesn't seem to be anything stored against either
> fktab1 or fktab2 in the ADOX model that I can find, even spelunking
> over the properties collection revealed nothing.

> Incidentally, where on earth is Access storing this metadata. I can't
> find it in MSysObjects and although some articles in the KB refer to
> MSysIndexes, this table doesn't seem to exist. In any case, querying
> MSysObjects through ODBC is refused with a permissions error, even when
> connecting as Admin. Does anyone know how to get metadata out of Access
> other than using ADOX, convenient though it undoubtedly is?.

The schema rowsets will be your most generic, extensible mechanism.

Conor

- Show quoted text -

Quote:

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

1. ADOX.Indexes/ADOX.Key/Foreign Keys

I am trying to generate a VB program which analyses an existing MDB and writes the VB
source code necessary for creating the MDB from scratch using ADOX. I have hit the
following problem when looping through the Indexes collection of the Table object:

When you create a relationship (i.e. under Access) between 2 tables, an Index is created ,
but the index is *not* visible to Access.  ADOX *does* see this index but the ADOX.Index
object does not have a property for distinguishing between an Index which was created in
order to establish a relationship between tables.

Example:  I visually create the following relationship under Access:

Customers.ID   ----- 1:n -------> Orders.Cust_ID   (this will create the invisible index!)

Under VB, when I loop through the oCat.Tables("Orders").Indexes collection, I will find an
index called "CustomersOrders", kindly supplied by Access behind the scenes.

Using the Indexes collection I need a way of sifting out indices which were the result of
creating a relationship between tables. How? The Index object does not have a RelatedTable
property.

TIA,
-Toby

2. How to force T-SQL to execute jobs in synchronous mode?

3. retrieving foreign key info

4. School program

5. Info about Foreign Keys via ADO ?

6. Char. not supported?

7. Copying relationships/foreign keys with ADOX

8. Popup fonts FPW

9. ADOX, SQLOLEDB and Foreign Keys

10. ADOX: Creating Foreign Keys

11. Retrieve original field names from view via ADOX

12. Trigger to check a foreign key without using foreign key