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