Long Text Tables (1 Viewer)

Agree with essaytee regarding the use of both RI and avoiding the use of unbound forms.
Unbound forms take far more development time than bound forms.
They are almost always not necessary and certainly not required to protect existing data from being edited or deleted.

I'm happy to take the OPs word for it that this system works. However, I still think its adding complexity for little or no benefit

We don't use Data Entry mode ever. New Records in major table are made through SQL or DAO code. It does require more coding, but once you've been doing it that way for years, the gap is not as large.

The main advantage of the Unbound form is that it allows complete control of data being entered into multiple tables at once. many of our forms will make entries into 10 or so tables in a single entry.

Many of these entries are many to many. Many to Many must have a query. Otherwise how do you get at least 2 foreign keys from 2 different tables? And if you delete a record from a Link Table, it will not delete either of the Parent Records, nor would you want it to. That's a major reasons for many to many. If you delete a Parent Records you could use RI to delete the Link Record, but once again we allow 0 deletions in our systems. So why do we need to use RI?

We don't use Data Entry mode ever.

Click to expand...

Access is a RAD tool. When you choose to not use its rad features, you give up the primary reason for using Access at all. You are left with all the constraints and none of the benefits.

There is no issue with building forms to support m-m relationships. Here's a sample that shows two techniques - a pop up form and a subform.

Cascade delete allows you to delete child records when you delete a parent record. Enforcing RI without cascade delete prevents deleting parent records when there are any child records in ANY related table. Cascade delete is a double edged sword. I use it whenever possible but you do have to understand the ramifications.

Why to use RI? Primarily because it is there. it is a silent guardian of the data. It is not a complete solution because there are many business rules that it cannot enforce but it does enforce all generic relationship rules.

To even ask this question means that you are in an isolated environment and don't have interactions with other departments. Because of your design decisions, if some other department requested access to "your" database, you would not be able to support the request because you could not guarantee that they would understand and employ the necessary techniques to protect "your" data. On more than one occasion, I have helped clients out of this quagmire. At one UTC subsidiary, they had 25 part master files. Think about the waste of time maintaining all these databases plus the potential for discrepancies. With an insurance company, it was 12 Customer files. Fixing problems like this is enormously expensive but the separation causes many companies to lose business opportunities and make poor decisions. This is the primary reason that ERP systems became popular. The company's own internal systems were so fragmented that they couldn't talk to each other. So, even though ALL the popular ERP systems are lacking in individual areas and the company's original custom built systems were far better at supporting the company needs, the ability for systems to communication ultimately outweighs the custom features of the in house applications.

That was pretty long winded but the point is - think beyond your enclosed ecosystem. If some other group wants to use your "master" file, you need to be in a position to support that and without RI, there is no chance.