Question Should commandment 2 be rewritten? (1 Viewer)

[NotSoRandomOne]

I'm looking at the ten commandments listed, and the second one says to never let users directly edit the tables. Should it be revised to state that you should never let the users access the tables with totally bound forms (if that is the right jargon to use)?

The reason I'm asking is to know if I'm missing something fundamental. If I create a 'Customers' table, and then use Access's wizard to create a 'Customers' form, which simply uses the fields in the table (as the default wizard setting does), there doesn't seem to be any difference between the table and the form. The user can open the form and delete all of the existing customers if they want, by highlighting everything and deleting.

Is there a resource somewhere that goes into this more deeply?

Thanks,
David

 

[Scooterbug]

When using forms, you are not letting the users access the table directly. What Commandment 2 means is to not let them access the tables via the database window. Via the database window, they could possibly change data types, delete or add fields, etc.

In your example, you can take away the ability to delete data via the Forms properties.

 

[Atomic Shrimp]

There are things you can do in the datasheet view of a table that you can't do in a datasheet form - for example, you can delete or rename a column. You can also quite easily switch to design view and really wreak havoc.

Those (IMO) are the kinds of reasons not to let a user ever touch the forms. But yes, implicit in the commandment is the fact that you can add all kinds of layers of control and action to a form that you simply cannot do to a table.

 

[Banana]

While I personally prefer that all bound forms use queries, there is no issues with binding the form to the tables.

The intent of #2 usually pertains to the need for validation and controlling data entry, which is always had by using a form. When a user opens a table directly, there is very little you can do to validate the data and you can forget about getting user to follow a certain business rule that's not easily expressed in a single validation property.

Even though you may use form wizard and the result seems identical, you still have the ability to manage data entry and use events such as BeforeUpdate. Those simply are not available when you open table directly.

 

[NotSoRandomOne]

Thank you - that helped me see the light more clearly. On a similar note, in the same commandment it says that you shall "Abhor" the use of lookup tables. Does everyone really "Abhor" them?

Even the sample Northwind database uses them, as they are a tool. Without them, you would have to store "My Ridiculously Long Company Name" in every order table, and other place that requires usage of that information. Certainly, this will take up the 2GB limit much more quickly than storing an integer? (Not to mention that if someone changes their company's name, or if it was entered incorrectly to begin with, then you have a real mess on your hands.)

Should that part of the second commandment really say that you shall use lookup fields with care and forethought, but not with abhorrence?

Thanks!
David

 

[Scooterbug]

the lookup tables abhorred are at a table level. Creating a separate lookup table and linking them via an ID number is the recommended way to go.

 

[boblarson]

Thank you - that helped me see the light more clearly. On a similar note, in the same commandment it says that you shall "Abhor" the use of lookup tables. Does everyone really "Abhor" them?

Not abhor lookup TABLES but lookups defined at table LEVEL.

Even the sample Northwind database uses them, as they are a tool.

Yes, and us MVP's have been very vocal to Microsoft about that. We don't like that they do stuff like that as it is definitely NOT best practice.

Without them, you would have to store "My Ridiculously Long Company Name" in every order table, and other place that requires usage of that information.

No, you store the ID's and use lookups at FORM level to identify things and with QUERIES.

Should that part of the second commandment really say that you shall use lookup fields with care and forethought, but not with abhorrence?

Nope, not at all. But lookups can be defined at table level to help you with creating your forms as they will create your combo boxes for you. But, as soon as that is done REMOVE THEM.

Bob Larson
Access MVP 2008-2009, 2009-2010

 

[Banana]

As Bob said, Northwind wasn't really that great example of how to do things right. It's a good example of what you can do in Access.

But we all know just because you can do it, doesn't mean you should do it.

 

[NotSoRandomOne]

Again, thanks. That helps clear my confusion.

Best wishes,
David

 

[NotSoRandomOne]

... But lookups can be defined at table level to help you with creating your forms as they will create your combo boxes for you. But, as soon as that is done REMOVE THEM...

To remove them, is the only thing that must be done is to change the Display Control in the Lookup tab from Combo Box to Text Box? I'm not asking about what it takes to rewire everything afterwords - simply the removal process.

 

[boblarson]

To remove them, is the only thing that must be done is to change the Display Control in the Lookup tab from Combo Box to Text Box? I'm not asking about what it takes to rewire everything afterwords - simply the removal process.

Yep, just change it to Text Box from Combo Box.

 

[NotSoRandomOne]

Thank you. Back to work for me...

 

[Galaxiom]

But lookups can be defined at table level to help you with creating your forms as they will create your combo boxes for you. But, as soon as that is done REMOVE THEM.

I just wanted to reinforce Bob's comment here. The "abhor" commandment probably frightens new developers unnecessarily.

Temporary table level lookups save considerable work during form design. They also make it easier to get a feel for the data in the main tables during the design phase.

They also have a purpose for "slapped up" developer use only databases with only tables and queries. I have a crude database like this where I didn't bother to make forms.

But they definitely should be removed once the initial design phase of a production database is completed. Removing them just before the database is split seems a good practice to me.