[AccessD] The Famous Bound/Unbound Debate

Thanks Jurgen!

John W. Colby
www.ColbyConsulting.com

On 9/13/2010 5:26 PM, Jurgen Welz wrote:
>
> I don't believe the number of records in a table has any bearing on advantages of bound or unbound as the recordset of a bound form may be set to a single record regardless of number of records in a table.  For example, if you use a search or lookup combo at the top of a form or in its header to navigate to a record, you can use the classic Northwinds findfirst in a recordset clone and on not nomatch set the bookmark of the form recordset to the recordset clone bookmark.  It is equally valid to set the recordset in the lookup combo after update event to:
>
>
>
> "Select * FROM tblX WHERE PK = "&  me.cboLookup
>
>
>
> where cboLookup is bound to a hidden first column containing primary keys and visible subsequent columns containing sorted readable key data.
>
>
> It is also possible to create a bound form to display one or more records of data not stored in any table; IE, you can push array data directly into a bound form in order to take advantage of built in navigation controls and record selectors you would need to code if you wanted them in unbound scenarios.
>
>
>
> In my opinion, the most onerous task a developer takes on when choosing to attempt the unbound route is to manage concurrent unsaved edits in a multi user environment.
>
>
>
> It is possible to create a concurrent edit resolution system in an unbound system, but using bound forms avoids the need for high level expertise to adequately manage concurrency issues (last person to save a record overwrites any changes made by other users in the time between last person loading the record and closing it with any change to the data without notice).  I have devised a scheme or two myself as I work in a 60+ concurrent user envirionment where many of those 60+ people will be having to work with the same 200 active (40 highly active) projects under construction and 100 or so bids pursued in a particular week.  Nearly all of my data entry and edit forms are currently bound and my users are trained to select the 'Drop Changes' when they get the automatic built in edit conflict notification, but training only takes you so far.  It's nice for a user not to have to drop changes when he edited an address in a record just after the dollar value of a project was cha
nged by another user because your system detected that the changes to the data are in different fields and don't conflict with the changes you made.  If you let that site address revision wipe out the contract value because you simply over wrote the table record with your form data and haven't managed or failed to correctly manage that revision in an unbound 'last edit' wins scenario, you may have just cost somebody a lot of money.
>
>
> Ciao
>
> Jürgen Welz
>
> Edmonton, Alberta
>
> jwelz at hotmail.com
>
>
>
>> Date: Mon, 13 Sep 2010 14:32:22 -0500
>> From: garykjos at gmail.com
>> To: accessd at databaseadvisors.com
>> Subject: Re: [AccessD] The Famous Bound/Unbound Debate
>>
>> Hi Brad,
>>
>> Bound refers to using a record set, possibly a table or maybe a query
>> as the record source for an Access Form. This has the advantage of
>> letting Access take care of a lot of functionality.
>>
>> Unbound refers to not having a record source for the form and then
>> using code behind the form to populate the various controls on the
>> form. Doing this requires some amount of work to populate those
>> fields, detect changes to them the user might make and then making the
>> corresponding update to the records in the data tables.
>>
>> If you are using Access built in Jet Database as the data storage for
>> the application and you don't have that many rows in your tables, most
>> would agree that bound is the way to go. But maybe not ;-) If you use
>> another database engine to store the data, perhaps SQL Server, it
>> could be argued that it's much better to only retrieve the individual
>> records you are wanting to display and or change and so doing that
>> with code and then populating the controls on the form might be a
>> better way to go.
>>
>> <SNIP>
>> GK
>