Unbound forms and data integrity issues

[papichulo]

Hello everyone,

I have a question regarding the use of unbound forms and data integrity
issues. I just started a new job with an employer that uses MS Access
heavily for our call-center at 3 locations. It was told to me that all of
the forms that are created are unbound because there are data integrity
issues which arise when multiple users are using the same database. I've
worked with Access for a few years and haven't heard of this coming up.

I just wanted to get you input as to the validity of this issue.

Also as a side question, what are the pro and cons to using unbound forms as
opposed to bound forms.

Thanks,
Jose

 

[Allen Browne]

If you are working in a company that has particular operational practices,
you probably have to work within those guidelines.

As an independent developer, if I were asked to develop an Access
application for someone who insisted every form was unbound, I would
probably walk away from the job. To stay with the project, I would increase
the estimate for the project by a factor of 4 or 5 times, because it is
going to take 4 - 5 as many hours to develop it.

One of the brilliant things about Access is the event of the bound forms,
such as Form_BeforeUpdate for record-level validation. If you throw all that
away, you lose all that plus continuous subforms for showing related data.

Optimistic locking (the Access default) is *very* well tested. Any developer
who thinks they can do a better job suffers from pride and ignorance.

For large datasets (hundreds of thousands of tables), you could make a good
case for binding a form to a single record instead of an entire table. But
that is still a bound form. Discard bound forms, and you would actually be
better off developing in pure VB or some other non-data-centric environment.

 

['69 Camaro]

Hi, Jose.

It was told to me that all of
the forms that are created are unbound because there are data integrity
issues which arise when multiple users are using the same database.

Data integrity issues arise from unnormalized tables and when referential
integrity is not enforced on related tables. It's possible that referential
integrity cannot be enforced because tables within a relationship are linked.
When this is the case, "business logic" (i.e., application coding, such as
code behind the forms) is used to ensure referential integrity -- but there
is no guarantee that this will always work. Only the database engine can
guarantee that referential integrity is enforced. It's best to place all
related tables in the same database file so that the database engine can
control referential integrity, but this isn't always possible with large
tables, as Jet 4.0 has a maximum capacity of 2 GB. Hence, the linked tables
issue with referential integrity.

That said, data integrity issues usually come from unnormalized tables, but
when they're from referential integrity not being enforced, in the cases I've
seen, three out of four times it's because the database developer wasn't yet
skilled enough to know that referential integrity must be enforced.

Also as a side question, what are the pro and cons to using unbound forms as
opposed to bound forms.

Bound forms means that RAD can be used for quick application development and
programming maintenance. Since the fields are bound, record locking issues
may occur much more often than when using unbound forms. The record is
locked as soon as a user starts editing it, while with unbound forms, the
coding logic can ensure that the record is only locked during the update,
add, or delete operation. However, an adept database developer can avoid
most record locking problems, even in high concurrency/high usage
applications.

Unbound forms take a great deal more time to develop, because code needs to
be written (and tested) to provide the same functionality that bound forms
provide. Unbound forms that update/add/delete records are often used to
provide the business logic that will attempt to enforce data integrity, a
practice that isn't guaranteed to be successful.

However, since this is a new job, you need to be careful of stepping on
toes. Questioning the validity of a mission-critical application that may
have been built in-house where a lot of pride has been instilled in it may
not be in your best interest.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.