Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-15-2019, 07:00 AM   #16
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,553
Thanks: 13
Thanked 1,452 Times in 1,384 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Long Text Tables

Quote:
In a system where you are required to develop to avoid into the Maximum Open Databases limit.
Good application design would control this. Users get confused when too many forms are open at once and so most applications limit the number of open forms. Once you do that, the problem of too many tables open goes away.
Quote:
We don't really use the built-in referential integrity anyway.
RI is there for a reason. The reason being to protect the integrity of data. It's a dirty job but someone has do it. If the database engine does it in one place, you don't have to do it in hundreds of places.

I don't like the multi-use solution you have chosen but at least you have made it possible to use RI by defining a unique tableNum and an intermediate m-m table. To enforce RI, you need to include that dummy field in every "from" table. That way you can make the tableNum and FK unique in the m-m table and enforce RI on the two column unique index. Normally RI involves the primary key of a table but in this case, you would reference the unique composite index.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-15-2019, 01:44 PM   #17
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,640
Thanks: 50
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: Long Text Tables

I agree about the Maximum. We have been using more popups and less subforms behind Tab Controls. Multiple subforms on Pages of Tab Controls should be avoided for a number of reasons. It is about the easiest way to bloat a system.

It takes building one before you believe just how bad it gets.

Having orphaned records is not any kind of problem at all. Like I said, we use queries for data entry, and never allow deletions.

Never allowing deletions is a fundamental requirement of mission critical data. I can't even imagine the problems that would cause.

"What happened last week, when we sent that shipment to Algeria without Prepay?

I don't know, what shipment?

Yeah, not good.
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old 04-18-2019, 09:20 AM   #18
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,553
Thanks: 13
Thanked 1,452 Times in 1,384 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Long Text Tables

Someone can and will break RI on any app where you have rolled your own. It is hubris to think you can do this better than the database engine.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-19-2019, 12:42 PM   #19
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,640
Thanks: 50
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: Long Text Tables

Quote:
Originally Posted by Pat Hartman View Post
Someone can and will break RI on any app where you have rolled your own. It is hubris to think you can do this better than the database engine.
It's not a matter of better. It's a matter of more useful.

1. This Multiple Field Foreign Key arrangement has proven to be very effective in creating much needed flexibility.

2. Using Queries to replace putting Forms in Data Entry Mode has made them almost bullet proof when it comes to following business rules, and controlling the creation of records.

3. Replacing the traditional "Many-to-One" relationships with Many to Many, allows database design to mimic real world systems. Many to Many by definition can't really be controlled by the built in RI.

4. After so many examples of where it is not available to use, we simply no longer rely on it.

It may be arrogance, as you say. But, what we do works, and in the last couple of years, hundreds of thousands of records have been put into our Access Databases and none of them have the problems you describe.

The main thing we do differently is to force All New Records to be entered through some kind of Query. Fill-in a bunch of unbound fields, on an unbound form, and voila, no direct access to tables = no missing data.
__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus

Last edited by Thales750; 04-20-2019 at 06:28 AM.
Thales750 is offline   Reply With Quote
Old 04-19-2019, 04:58 PM   #20
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 477
Thanks: 8
Thanked 112 Times in 108 Posts
essaytee is on a distinguished road
Re: Long Text Tables

Quote:
Originally Posted by Thales750 View Post
...snip...
3. Replacing the traditional "Many-to-One" relationships with Many to Many, allows database design to mimic real world systems. Many to Many by definition can't really be controlled by the built in RI.
I have to disagree, I utilise the RI (including many to many) in nearly every application. It works, it does what it's supposed to do. It might not be my first line of defence against incorrect data input but at least I know my error trapping routines will pick it up.

Do you have a scenario or test db that confirms your assertion that a many to many can't really be controlled by the built-in RI?
Quote:
Originally Posted by Thales750 View Post
The main thing we do differently is to force All Data to be entered through some kind of Query. Fill-in a bunch of unbound fields, on an unbound form, and voila, no direct access to tables = no missing data.
Yes, easy to do, and I do it myself for some applications, though have to qualify easy. There is a tad more coding involved but there is a basic template to follow so creation of new forms using this method becomes straight-forward. I still, as mentioned above, enforce RI of the database engine.
__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is offline   Reply With Quote
Old 04-19-2019, 11:54 PM   #21
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,910
Thanks: 105
Thanked 2,388 Times in 2,195 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Long Text Tables

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-20-2019, 06:52 AM   #22
Thales750
Formerly Jsanders
 
Join Date: Dec 2007
Location: Galveston, Texas
Posts: 1,640
Thanks: 50
Thanked 28 Times in 28 Posts
Thales750 is on a distinguished road
Re: Long Text Tables

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?

__________________
Aboard Oceans Eleven on Offatts Bayou
inveniemus viam aut faciemus
Thales750 is offline   Reply With Quote
Old Yesterday, 12:55 PM   #23
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,553
Thanks: 13
Thanked 1,452 Times in 1,384 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Long Text Tables

Quote:
We don't use Data Entry mode ever.
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.
Attached Files
File Type: zip ManyToMany_A2016_20171217.zip (1.51 MB, 2 views)

__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; Yesterday at 01:09 PM.
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Uncle Gizmo (Yesterday)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
In-text citations in long text field Harry Paraskeva Forms 5 02-08-2019 02:20 AM
Short Text -> Long Text PD1978US1 General 2 01-15-2018 04:46 AM
carriage returns not showing up in text field(long text) MilaK Forms 3 09-06-2017 06:04 AM
Question importing multiple rtf documents into rich text format long text field. niawo General 2 07-05-2017 02:50 AM
Query changing Data Type from long text to short text Galette Queries 4 11-12-2015 05:16 AM




All times are GMT -8. The time now is 08:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World