Titan Access and Jet P&FKey relationships

We distribute a database independant browser/updater
which currently supports Interbase (surprise surprise)
and Oracle.

We ship the tool as it reads the database structure at
run time, offering lookups for FKeys and other nice tools
to help the uninitiated maintain a datbase which has not
yet had its user interface bolted on.

I havn't been too shocked to find that some of our customers
are using Access and I decided to take the plunge and support
(?) Access.

I chose the Titan engine as Ferret (as the application is known)
makes use of the Infopower add-ons and I would be able to support
Access with as little pain as possible with good performance.
InfoPower support is not yet gold for Delphi 3 so that was out.
I also strongly resent Borlands pricing upgrade policy for
existing Client/Server users like me.

Full marks to Titan so far for delivering what they say.

0/10 for Access, however. I would appreciate some knowledgeable
enlightenment if someone would be so kind.

The problem is this: Ferret needs to quiz the system tables to
discover what tables access the table one wants to edit, and what
tables are accessed by the table one wants to edit. I also need
field ordering in the casze of multi element p/f keys. I achieve this
by using Oracles System table views, and the Interbase system table
views achieve the same thing. I enclose the Oracle and Interbase
queries for your amu{*word*224}t (and constructive criticism :-) )

[Oracle]
SysQuery=SELECT RCUF.COLUMN_NAME AS FKEY,
SysQuery=RCUP.TABLE_NAME AS PTABLE,
SysQuery=RCUP.COLUMN_NAME AS PKEY,
SysQuery=RCUP.CONSTRAINT_NAME AS PCONSTRAINT,
SysQuery=RCUF.POSITION FPOS,
SysQuery=RCUP.POSITION PPOS
SysQuery=FROM USER_CONS_COLUMNS RCUF,
SysQuery=USER_CONS_COLUMNS RCUP,
SysQuery=USER_CONSTRAINTS RC
SysQuery=WHERE RCUF.TABLE_NAME=:TableName
SysQuery=AND RC.CONSTRAINT_TYPE='R'
SysQuery=AND RC.CONSTRAINT_NAME=RCUF.CONSTRAINT_NAME
SysQuery=AND RCUP.CONSTRAINT_NAME=RC.R_CONSTRAINT_NAME
SysQuery=ORDER BY RCUF.POSITION

[Intrbase]
SysQuery=SELECT RCUF.COLUMN_NAME AS FKEY,
SysQuery=RCUP.TABLE_NAME AS PTABLE,
SysQuery=RCUP.COLUMN_NAME AS PKEY,
SysQuery=RCUP.CONSTRAINT_NAME AS PCONSTRAINT,
SysQuery=RCUF.FIELD_POSITION FPOS,
SysQuery=RCUP.FIELD_POSITION PPOS
SysQuery=FROM RELCON_COLUMN_USAGE RCUF,
SysQuery=RELCON_COLUMN_USAGE RCUP,
SysQuery=REFERENTIAL_CONSTRAINTS RC
SysQuery=WHERE RCUF.TABLE_NAME=:TableName
SysQuery=AND RCUF.CONSTRAINT_TYPE='FOREIGN KEY'
SysQuery=AND RC.CONSTRAINT_NAME=RCUF.CONSTRAINT_NAME
SysQuery=AND RCUP.CONSTRAINT_NAME=RC.UNIQUE_CONSTRAINT_NAME
SysQuery=ORDER BY RCUF.FIELD_POSITION

They both work fine(ish). How the blazes do you do this with
Access ? I thought it was a Relational database: I thought it
would be self describing. I bought the Ms Jet Database Engine
Programmers guide at huge expense which offered a hefty 1 page
on the system tables.

I really thought I'd cracked it when I checked out the
MSysRelationships system table; and lets not forget the
well handy MSysObjects system table. I marked them as readable
and I could query them just fine. Has anyone successfully written
code as either a pass through or whatever to discover this
information ? I'd accept a pork pie if it'd do the job.
I notice if you code directly with the DAO it can be achieved.
I am trying to avoid proprietary techniques like this.

I have an impatient user waiting for Ferrets cosseting help on
getting around a new big database. We are importing some of the
start up data into Access although the target is Oracle (so Ferret
will still come in handy).

Rgds, and thanks in advance, Paul.

--
Paul Ingram Group Ltd
140A High Street               email: firstname.lastn...@ig.co.uk
Godalming                      voice: +44 1483 424424
Surrey, UK                       fax: +44 1483 419419