Editing an Existing Access Database - Operation not supported

[Gicu]

On my website a have a free utility named FUZE that merges a selected front-end with copies of the back-end tables (basically it is the opposite of the built-in database splitter). So you end up with an all-in-one db having the latest front-end objects (if that is what you selected) and the latest back-end data. You would do your development in this file (as it is easier to modify local tables) then you would "de-fuze" it using the same utility or the db splitter.
https://forestbyte.com/ms-access-utilities/fba-fuze/


Cheers,

[yrstruly]

The DB relationships has been all done in the Fe it seems, nothing in the Be. What is the best practice for relationships in Access? Will it influence the existing Fe if i also do the same relationships at the back with added new one's?

[CJ_London]

Relationships can only be created/modified where the tables are - so if split, the back end. You cannot create a relationship between tables in different files. Don’t confuse relationships with joins, they look the same but are not the same

The relationship is defined in the relationship window - but if one or both tables are in a different db, the enforce and cascade options are disabled - the rest is about the type of join

[Micron]

You can create relationships in the FE when the links are valid. I actually would think it's where they should be, given that's where the queries, forms and reports reside. I might be wrong about the last part, but I do know it's possible.

[CJ_London]

I disagree - these two tables are linked tables where I've connected a PK to a FK in the FE - note the bit that is highlighted, it is disabled. Yes a one to many 'relationship' is created, but that is all a join is. You are unable to set any relationship rules.


I guess if you are not managing the relationships in any way, but just want a default join to show when creating a query (i.e. you don't want to enforce any relationship rules), you can do so in the front end, but be clear that is not a relationship. If your PK is editable, you will need to write code to update the FKs. If you delete the primary record, you will need additional code to delete the child tables (assuming you don't want orphan child records).

[Micron]

It seems you are correct. What I think I was looking at in the fe is the relationships set in the be. I guess they propagate forward (I'm getting forgetful ). The be has referential integrity set, and so do the fe relationships.

[isladogs]

This is a quote from the first part of my 3-paart series of articles: Relationships1

For linked tables, the relationships need to be created in the linked backend database
You can display a backend table relationship in the frontend relationships window.
However, doing this will NOT over-ride any relationship already created in the backend