Table Relationships... Front vs. Back

[Kaloyanides]

Can someone talk to me like I'm an idiot and tell me how table relationships work when comparing front-end vs. back-end?



I don't get it...

I get the purpose of relationships.

What I don't understand is why there are a set of relationships in the back-end and another set of relationships in the front-end?

From my research, you always use the relationships in the back-end. But this doesn't make sense to me because those relationships are not carried into the front-end?

Had a database.
Added four new fields to the ItemMaster in the back-end.

CategoryID
SubCategoryID
ClassID
SubClassID

On the ItemMaster form, the SubCategoryID is limited to only those matching CategoryID. ClassID to only those matching SubCatID. SubClassID to only those that match ClassID. If that makes sense.

I have a form for editing the categories and classes.

It has a subform/subreport with source object set to Table.tlkpProductCategory.

In my test database that I used to develop these features, the + sign shows next to each category so user can click and expand and see all SubCat's associated with the Category selected. The user can then click the + sign next to the SubCat to see the Classes associated with each SubCat and so forth...

But when I imported all of this into the live database the relationships are not being pulled into the front-end. They're defined in the backend. I even tried manually defining them in the front end too but that doesn't even work. I tried creating a series of queries to bypass the need for relationships at all and that's not working...

Sometimes Access is very frustrating....

If anyone can shed some light on this nightmare, I'll be eternally grateful!

Many thx in advance...

[ranman256]

Theyre supposed to carry thru to the FE. Mine do.
maybe refresh on the FE, use show ALL button.

[Kaloyanides]

Yeah, for some reason they don't...

I'm wondering if I should start from scratch. Create a new database and import everything into it. Front and Back. This is the second funky issue you've helped me with. The last issue was when I added categories to a report as group I got decimal error even though no decimals. Never got that working either.

Anyway, see pics below. Thx for your help as always...

This is a pic from the front end:


This is a pic from the backend:

[aytee111]

As you can see, the front-end doesn't allow editing the relationships. Set up the relationships in the back-end and leave it at that. Maybe recreate the FE and relink all the tables. Make sure you have no lookups on tables, these will cause some weird problems to happen.

Don't give the user access to the tables, as in being able to click the + sign, provide them data only thru forms which you control thru the form design. Use comboboxes for them to select the relevant id values.

Keep it as simple as you can, do not use what we call the "user" capabilities of Access, that is for very basic systems and yours does not fall into that category. Especially the table design, no lookups, no formatting, no multi-value fields, no calculated fields. Just the bare bones. Then you will have much better control over the data and how/when it is maintained.

Also be careful of setting auto-updating and -deleting in your relationships as you have it defined above. It is better for you to control when records are updated and deleted rather than leaving it in the hands of Microsoft!

Yes, it can all get very frustrating!

[Micron]

This is interesting. I would have thought that with respect to relationships, the fe would treat the be tables the same as any other aspect that involves linked tables. Access considers a linked table as being native in so many ways that I'd be surprised if those capabilities were not extended to defining relationships on linked tables. I often didn't bother with relationships since anyone doing what I did would have been as cognizant of the relationships as I. To understand that would require delving in to the nature of the business, which isn't really necessary here. Anyway, I did search my archives and found that I had even set relationships in an fe for ODBC linked tables once. Since all the operations are taking place in the fe, I see no reason to put the relationships in the be. Nor do I see why those relationships couldn't be edited in the fe when the tables are linked. I know I can edit those ODBC relationships in the fe, so I'm a bit puzzled.

[June7]

Also did some testing on my split db. Relationships set in FE for linked tables do not allow changing referential integrity and cascade properties, even if no relationship was set in BE.

[Micron]

Was that testing done where the links are still valid? I see that I also cannot edit the RI or cascade properties but I can edit the joins. However, there's no way I can have valid links on these tables at home. Just wondering if that has anything to do with it.

[June7]

You mean table links to the backend? Yes, valid.

[Micron]

OK, thanks.