[AccessD] Performance tips anyone?

Hey Jurgen, long time no see.  Welcome back.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Monday, July 09, 2007 8:26 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Performance tips anyone?

I don't understand the comment about 'goofiness' with callbacks and debug  I
use callbacks more often than not and have never had a problem with breaking
into code.

I've said before what I say again below, and it needs to be repeated.

My concern with unbound forms is the scenario where two users open the same
record and each performs an edit.  Generally, unbound edits are written when
the form closed or unloads.  In such a case, the last user to close 'wins' 
and the first user's eits are lost without notice.  In the environments
where I have worked, we frequently have multiple users working in a single
record.  User 'A' will open the record to update an address.  User 'B' will
open the record to update a contract value field.  User 'B' will close the
record and his edit is writen to the table.  User 'A' answers the phone and
closes the record a moment later and the database is updated with the
current unbound form data.  The record has the updated address but the
change to the contract value is lost and no one is warned.

Sure you can write code to handle the situation.  For example, you could
update only edited fields and the overwrite will only happen if two users 
concurrently edit the same field to different values.   Or you could add an 
edit flag and update it anytime a user has a pending edit and clear it when
the record closes.  But I believe Drew said earlier that since it's a Jet
BE, there is no difference bound or unbound in regard to locking. With a
bound form, you can get a notification that another user has edited the
record and gives 'A' the option to abandon the edit.  My users see this
message with a degree of regularity and they have been taught always to drop
their edits, reopen the record and then redo any required edits.

I have seen many unbound databases created by reasonably competent Access
developers who blithely ignore this issue.  They don't mention the
additional things that need to be done in order to ensure data integrity.  I
am not adverse to unbound forms and use them where appropriate.  If a person
is in a web driven database where a user may have a personal shopping cart
and need to log in to access his cart, no problem with unbound.  If you take
appropriate steps to ensure the integirty of the data, no problem.

My primary reason for working unbound has been the formatting limitations of
bound controls in continuous forms and my environment has never permitted
3rd party controls.  I've heard reference to 'boundaries' or limitations of
bound forms but still manage to use them without compromising the user
interface.  You can even populate a bound form with a series of values that
did not exist in any table.

Some people assume that a bound form contains as many records as some table
to which a form is bound, but all of my parent forms are populated by a
single record determined by a primary key parameter.  The recordset is
determined in the open event and the recordsource property is empty.  It is
just as fast as unbound, and faster than unbound that includes the setting
of edit flags.  I have 47 concurrent users today and among the only unbound
forms I use today are a few that allow drag and drop of categories of
employees (4 categories, 4 colours in 4 columns, each its own subform) onto
and from a list of projects, and the dragged employees drop in to a subform
of projects containing sublists of employees that retain their formatting
where dropped.  I counln't figure out how to intersperse the types of
records with their various colours in a bound form.  And of course forms for
search, report selection, navigation, calculations, calendars and such
ancilliary forms that do not need to update their own values.

47 users, Access 2003, Access 2000 format, nearly entirely bound and no
reports of data conflicts that shouldn't be reported.  I anticipate more
users in the coming months and see no difference between the bound and
unbound approach in terms of locked data pages, indexes or corruptions
because my recordsets are tiny compared to the tables and rarely, but
occasionally, involve concurrent edits.

Ciao
J|rgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Drew Wutka" <DWUTKA at marlow.com>
>
>But Access is just as well suited for unbound solutions too.  The only
>exception to that rule is it's goofiness with callback routines. (Can't
>go into debug if you have a callback routine ANYWHERE.  Goes haywire).
>
>Drew

_________________________________________________________________
http://liveearth.msn.com