Viewing Access relationships using Business Objects?

[MrDeveloper]

Hi,
I have an Access database that does not have any relationships defined using the Relationships Window but functions correctly simply by virtue of primary keys etc.

I have then accessed the MDB via Business Objects to show these relationships which it does correctly, but it does not show any extra relationships that exist through the use of drop-downs (i.e. where an Access Drop-down pulls in values from another table to be placed in the master table).

The question is, to enable Business Objects to view these extra relationships, is it simply a case of defining the relationship formally via the Relationship window in Access? e.g. defining a 1-to-Many graphically even though Access does not see it that way?!

Any help - particularly from anyone who has used Business Objects with Access - much appreciated!

 

[Darrylles]

Hi,

I've no idea about 'Business Objects', but the way Access works is that the 'Relationship' table (window) is where Access picks up relationships 'by default'.
(It is better to think of the MS Access Relationship table as a documentation tool. i.e. defining relationships helps future developers understand the overall db design).

It does not enforce these relationships at all - it simply attempts to 'default' a relationship i.e. when creating a query etc.

At run-time, the MS Relationship table is completely ignored.

Where relationships exist within code, or queries etc that are not defined within the MS Access relationship table - your 'Business Objects' application CAN NOT pick up.
Furthermore, this B.O. application can surely only pick up relationships where key fields are identically named.
i.e. I use 'PK' as standard for all primary key field names in ALL tables, and 'tablename_FK' for foreign key field names in related tables.
B.O. I would assume CANNOT pick these relationships up.

The relationships that you want B.O. to pick up are called 'AD-HOC' relationships - i.e. they come into existence only at run-time, they are not pre-defined.
B.O. will never pick these up.

Read the B.O. documentation to establish EXACTLY what it is capable of.

Regards,

Darrylle





Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[willir]

MrDeveloper

Changing the design, i.e. after the database has gone into production is risky. Make a backup or two.

I have seen some real good database applications created without establishing formal relationships. The integrity is controlled at the form level through queries and amcros, may be some coding.

By establishing your relationships now will
- you will assume ownership of the database
- the database may continue to work without a hiccup, or there is a chance some parts may fail. (You may uncover a bug for example)
- you may not be able to enable "Enforce Referential Check" because of orphans or mismatched data.

To use your third party software, "Business Objects" may result in the same end results.

Work on a backup copy before proceeding, and remember you assume ownership if things get messed up.

Richard

 

[MrDeveloper]

Firstly, many thanks to you both for your help and advice - it was very useful and answered just what I needed to know.

I am new to using Bus.Objects so still working out its limitations, but you are right - I think it creates relationships based on 'hard knowledge' i.e. the presence of Primary Keys.

I have since learnt from another Forum that Bus.Objects can only see joins if they are manually created within Bus.Objects and that they have also got to be defined in Access first.

Good news about the Relationship window - means I can play with it without doing any serious damage (but back-ups will still be taken!).

The integrity is controlled at the form level in my application through queries and joins I put in the SQL, yes. So long as I dont enforce referential integrity in the MS Relationships Window (thankfully none is required in this application) then it should be safe to define the relationships using the MS Access tool then go on to create this joins within my Bus.Objects application for it to work.

Thanks again to you both - learning about the true raison d'etre of the MS Access Relationships Window was very valuable!