Question about Editing data & Locking Records

[Rob Hofkens]

Hello everyone

I have a form to edit and add data using the unbound controle methode.
The code I created gets the record data from the table and puts it in the
unbound form controls.
Then I close the recordset.
The user can change the data and when he presses the save button I just open
the recordset again and update the table.

Since my database is going to be used in a multi user network I wonder if I
should use another aprouch?
I was thinking if it isn't better to open the recordset (in the Form.Open
section for instance) and to keep it open until the user has finished
editing ?
This way, I figure, the record is locked for other users to be edited at the
same time?

Maybe someone can advice what to do ?

Thanx in advance,

Rob.

 

[Allen Browne]

Rob, why would you bother doing this?

If you want to go through the whole process of using unbound forms, unbound
controls, handling your own validation of data types and sizes, retrieving
data, writing it, verifying that the data is still current (i.e. that no
other user has changed it in the mean time), and handling locking
considerations appropriately, why use Access? You just lost nearly all of
the RAD aspects.

Access is designed as a multi-user product. You can even choose from 3 kinds
of locking strategies and select from page- or record-level locking as
appropriate. Without having to write a single line of code.

Perhaps I'm just too lazy to bother reinventing the wheel.

 

[Albert D. Kallal]

Well, when you open the reocrdset, it takes extra bandwidth.

then, when you close the reocrdset. it takes extra bandwidth.

When you open it again, you use extra bandwidth.


I am kind of wondering, why not use abound form, and eliminate all of the
above open/close stuff.

Further, record locking is built in. If you enable it, when two users are
editing the same record, the other users when attempting to edit the record
will see a "lock" icon on the left side of the screen (in the record
selector - it looks a like a ghostbuster icon with "slash" through the
middle).

In addition, not only does a bound form do all of the above for you, but you
also don't have to write that whole whack of code you have now (that time
and money could have been used to feed the poor).

Maybe someone can advice what to do ?

My advice would be to use a bound form, as you save code, time, locking is
built in, and also save the network time of oopennog and closing reocrdset
which has additonal overthead...

Thanx in advance,

I not trying to be hard, but you have make a REAL GOOD case as to why you
dumped bound forms, and are coding all of this stuff? If you going to write
all of that code, then you should using something like vb6, or even vb.net.
The reason for this is those platforms ARE OPTIMIZED FOR UN-BOUND forms. So,
the .net tools have ALL KINDS OF WIZARDS and designs etc to deal with
un-bund forms. with access, it is OPTIMIZED for bound forms. When you
code/write your data update routines with un-bound forms in ms-access, you
are in the WORST position.

ie: it takes more work, and no wizardry or development tools
are provided for un-bound forms
The build in bound forms have a incredible rich event model (you
have before update, after update, on current...a huge huge list of features
of which NONE you can use when you go to un-bound forms. lets not even
mention the 3 delete events


on delete
Before del confirm
After del confirm

So, we have 3 events just for the deletion processes....


So, really, you get the worst of both worlds - no support/wizards for
un-bound, and you loose all of the features (events) that bound forms give
you.

If you are developing applications in with you need un-bound forms,
ms-access is the wrong tool, and your applications will actually cost you
MORE then if you pick a tool that is designed around that concept.

 

[Rob Hofkens]

Hi Allen , thanx for answering me !

Well I have this problem with some big lookup tables.
I started out with bound forms but I got performance problems.
I have this main form that I use to edit/add customers to the database and
has about 4000 records.
I am from the Netherlands and I have all the cities names in one table which
has 3000+ records.
In the main form I have two fields which have a relation to the city table.
So I used 2 listboxes on my main form which have a query to the city table
as source.
Technicaly it worked but it was also very slow like up to 5 seconds and
sometimes longer before the form was fully drawn on the screen.
This wasn't acceptable.

In previously asked questions on these newsgroups someone suggested unbound
forms and a popup form with the city names in it.

So now I am trying and use two unbound forms, one for looking up customers
and one for editing/adding just one customer at a time.
On the "look Up" form I have a button that opens the "edit/add" form with
the record data the user has currently selected.
So far I have no performance problems anymore but I am not done coding yet
and have to see how it turns out eventualy.
For the previously troublesome listboxes in the bound form I want to use
normal textboxes and when they get focus I want some kind of popup form that
enables me to browse through the city table/query from where the user can
select a city.

So thats how I ended up using unbound forms.
Maybe there is an alternate solution to my problem so I can still use bound
forms.

Rob.

 

[Rob Hofkens]

Hi Albert !

Looks like I am realy going the wrong way after reading your reply here.
I put some more info in my reply to Allen's reply for why I used unbound
forms in the first place.

Thanx for answering my question !

Rob.

 

[David W. Fenton]

So now I am trying and use two unbound forms, one for looking up
customers and one for editing/adding just one customer at a time.

I don't understand why you would need to make the lookup form
unbound. What advantage is there in that context, given that most of
the time you won't be editing or adding data to the list of cities?

I do add people in unbound forms, collecting the minimum number of
fields required to create a new record. I then take those values and
test them against existing data to see if there are any possible
duplicates. If the user determines none of the near matches is a
real duplicate, she can then create the real record, which is loaded
in a abound form.

I don't see where the problems you are describing are going to be
helped at all by going unbound.

 

[Bob Hairgrove]

Well I have this problem with some big lookup tables.
I started out with bound forms but I got performance problems.
I have this main form that I use to edit/add customers to the database and
has about 4000 records.
I am from the Netherlands and I have all the cities names in one table which
has 3000+ records.
In the main form I have two fields which have a relation to the city table.
So I used 2 listboxes on my main form which have a query to the city table
as source.
Technicaly it worked but it was also very slow like up to 5 seconds and
sometimes longer before the form was fully drawn on the screen.
This wasn't acceptable.

In previously asked questions on these newsgroups someone suggested unbound
forms and a popup form with the city names in it.

Are the city records in an external database? From your description of
this, it sounds like Access is pulling the data in over the wire,
perhaps from an ODBC data source or perhaps from flat files with no
primary keys or unique indexes.

If this is the case, you should probably try to cache the data in a
local Access table at startup and bind the form controls to that
table, not to the external database. This is helpful for data such as
list of cities which hardly ever change.

 

[Albert D. Kallal]

Well, I have some tables with 100,000 records, and they are consider
SMALL.....

With only tiny tables of 200,000 records, you should not be experiencing any
kind of noticeable delays here at all....

However, a combo box, or a list box is good for about 100, maybe 200
records. After that, even if it performs well, it is sheer torture to the
user to wade though such a huge list.

For high performance searching of a name(s), take a quick read of the
following:
http://www.members.shaw.ca/AlbertKallal/Search/index.html

I *might* consider a combo box based on that list of 3000 to select, but it
is a bit large....

 

[Allen Browne]

Okay, Rob. Thanks for clarifying.

If performance is the issue, the bottleneck is probably the combo. Once you
get many thousands of records in a combo, it really does slow the form down.
A technique I use sometimes is to delay-load the combo, i.e. there is
nothing in the drop-down list until the user types the first 3 or 4
characters. At that point the list loads with only the few hundred cities
that match. Here's an example of how that works:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

As far as bound forms go, you can have hundreds of thousands of records in a
table, and Access will display the form almost instantaneously in a
well-designed database. (It actually just displays the first records before
it has loaded them all. You can see the others are still being retrieved,
since there is not yet any value after the "of" in the navigation buttons at
the foot of the form.) Please understand that I am not really advocating
this as a great way to design the interface; what I'm saying is that Access
copes amazingly well, so if someone advised you to dump bound forms to gain
performance, they did not understand Access.

The kinds of things that will make a form slow to load are:
- Lots of combos and list boxes, since each one has a row source that must
be loaded.

- Many thousands of records in the RowSource of the combos/list boxes.

- Lots of subforms, since each of these also has records to be loaded, and
may also have their own combos/listboxes. (Typically people put these in the
pages of a tab control.)

- Inadequate indexes on the fields used for sorting and criteria, or lack of
correct relationships.

- Basing the form on badly designed queries that can't take advantage of the
indexes (such as using the Like operator everywhere, VBA function calls,
performing string comparisons on numbers/dates, ...)

- Failing to turn off subdatasheets.

- Leaving Name AutoCorrect turned on.

For general suggestions, Tony Toews has a good Access performance FAQ:
http://www.granite.ab.ca/access/performancefaq.htm

Hope that helps. No point buying one of these nice electric breadmakers, and
then building a kiln to put it in.