Viewing Access relationships

[MrDeveloper]

Hi,
I have an Access database which I connect to using Business Objects for reporting purposes. The database consists of a single master table and several smaller tables. The master table brings in selected values from the smaller tables by way of a Drop-Down list in an Access form.

No referential integrity is required (i.e. a record in the master table can exist without values from the smaller tables).

I have not set-up any relationships in the Relationship window of Access but Business Objects needs to recognise these 1-to-many links.

In order for it to fully 'see' these relationships, what do I do? The system works perfectly without needing to specify anything in the Relationships window of Access.

Any help much appreciated (I am fairly new to connecting BO and Access together).

 

[jobjoris]

I don't undestand your question? You want to add/detect joins or something?

 

[MrDeveloper]

Sorry - it was not very well described!

The relationships in the database were never defined using the Relationships Window in Access (not my preferred method of doing it). Through SQL joins in code and Primary Keys the system works as intended. However, when Business Objects is used to analyse the MDB file it finds these relationships ok but NOT any 'implied' relationships - i.e. the ones where values from sub-tables are dropped into the Master table at run-time.

I think the two issues are:

1. To get Bus.Objects to see relationships in Access that only really occur when the application runs, is the Relationships Window the best way to do it? I.e. can Bus.Objects 'see' any links defined using this window tool.

2. If relationships are created in the Relationships window in Access, will this damage my existing (and perfectly working) database?

I did a test for Nos.2 and so far I think it will survive so long as I dont enforce referential integrity. Whether Nos.1 will work or not, I dont know.

 

[blom0344]

Did you create a universe on the Access database? BO will not be able to 'see' the relationships in the Access database as it is created with the specific Access tool.

Relationship definition in Access is there for:

1. Enabling queries to be created without having to apply joins (join settings)
2. Enforce referential integrity / cascading deletes.

I think that on both accounts this should in no way effect how BO will read data from Access

T. Blom
Information analyst
tbl@shimano-eu.com

 

[jobjoris]

Creating Joins in your Universe will not change anything in your existing Access Database. I don't know if BO can see these 'implied' relationships with detect joins (I think it does), it looks at FK's and columnnames. But you should be able to create these joins yourself, it's not that hard is it?

I think your second question is a question you should ask on an Access-dev.forum, I realy wouldn't know...

 

[MrDeveloper]

Firstly, thanks to everyone who replied. Your advice was much appreciated.

I am new to Bus.Objects so I was not aware that joins could be enforced within the Universe. I will look into doing this as a way round the problem.

I have posted a query on an Access forum and found that creating these relationships in Access will not harm the existing database structure, which is good.

I am concerned about what has been said above though - that BO will not be able to 'see' the relationships created within Access - seems its up to the Universe to do this (i.e. creating the joins manually).

Dont know how to do this but I'll find a guide somewhere on the web Im sure.

Many thanks to you both again.

 

[blom0344]

Setting up joins between tables in a universe is the essence. Designer will allow you to drag and drop between two fields to create a join and you can set properties in a similar way , like you can do with Access.

I would encourage you to take a formal training course. That are a lot more things of importance when working with designer you will want to miss............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[jobjoris]

I've completed a short test of creating joins in the Access-database. It seems that the functionality of "Detect Joins" (under "tools" in your menu) gets the trick done if key's are generated in Access and the columns which are linked to eachother in the facttable have the same name.

Creating joins manually with one facttable (no contexts needed) is realy easy. Just select the tables to be joined, right-mouse-click, "join..." and select both columns...

 

[MrDeveloper]

How did you create the joins in Access - via the Relationship window?

Excuse my lack of knowledge here but when you talk about the facttable, I assume you mean in Bus.Objects (i.e. the columns linked to each other in Bus.Objects must have the same name)?

Does that also mean that once the join has been established in Bus.Objects in the facttable, it will work out a reverse look-up whenever a Bus.Objects report is run?

For example, you create a join between the main table Pupils:

Pupil Nos. = 254
Pupil Name = John Smith
Staff Name = 22

and the look-up table Staff:

Staff Nos. = 22
Staff Name = Prof. Smithers

in Business Objects, when you run a report, will it successfully determine look-up values and work out the full report to show a pupil as:

254--John Smith--Prof.Smithers

??

 

[jobjoris]

No, I'm sorry, I meant that I've detected the joins in Business Objects. I never made any relations in Access.

BO can detect joins on base of Keys and shared names. That's all. It will not effect your Access DB.

In your example, where you've got 2 tables PUPILS and STAFF, it wouldn't be able to create a correct join as the join would be between key-value STAFF.STAFFNOS and PUPILS.STAFFNAME but I assume you've just entered this incorrectly. If it is PUPILS.STAFFNOS than detecting the joins will probably be correct. Otherwise, you could create it yourself.

My "FACT"-table refers to your "Master"-table as I assume that all your measures are in your Master-table and all the "smaller" tables contain look-up values like pupils, staff, customers, etc.

 

[MrDeveloper]

So your test that worked was as follows?....

1. You set-up two tables in Access (a Fact-table/Master and a table of look-up values).

2. You named the columns in the two tables the same. E.g

Pupils Table:
Pupil_Nos = 254* *Set as Primary Key in Access
Pupil_Name = John Smith
Staff_Name = 22

Staff Table:
Staff_Nos = 22* *Set as Primary Key in Access
Staff_Name = Prof. Smithers

3. You did a Detect Joins in Bus.Objects and the relationship was identified ok?

This will be very helpful for me if these are the steps you took - would be grateful if you could identify any steps I missed in figuring what you did to make it work at a simple level (i.e. the naming of the columns in your test if this makes a difference to it working).

Many thanks,

 

[jobjoris]

Yes, correctly, except for the fact that I had NO Staff_name in my Pupils Table. I had a Staff_Nos in my pupils table (as I said in my prior post, I guess this is just a "slip of the keyboard" in your case...). This name equals the primary key in the staff table. Detect Joins worked ok.

 

[MrDeveloper]

Hi,
I fully understand now - I would like to say it was a slip of the keyboard but actually it was a slip of the mind! (i.e. I was thinking the foreign key you set-up was existing out of a link from Pupils.Column to Staff.Column)

I see now that you have created the FK by linking to the PK of the sub-table (I guess a true FK could not exist by linking to a non-Primary Key of another table).

Many thanks for your help and to everyone else who responded. Been a big help.