[AccessD] VBA Unbound data entry / update form

jwcolby jwcolby at colbyconsulting.com
Wed May 28 15:28:11 CDT 2008


Drew Wutka wrote:

 > Calm down amigo!

Drew,

 > The issue here is that you are using an .mdb, which locks 
pages when you edit a memo field, and because memo fields 
can run across multiple pages, you are going to get locks 
all over which interfere with other users trying to edit 
other memo fields.

Yep, and it is darned difficult to convince the client of 
all this.  The worst part is that (apparently) Access stores 
memos "haphazardly" out in an area of the MDB file similar 
to "the heap" in programming, then locks entire 4K (IIRC) 
"pages" of that "heap" any time that it has to write 
anything to that page.  This means that writing a memo in 
one table can lock an entirely different record in an 
entirely different table!  Or so it appears.

 >What you are asking for is NOT a simple 'unbound' 
solution, in fact, it's no solution at all.  Yes, you can 
mimic the capabilities of a bound form, using unbound 
methods.  But, it isn't going to fix your particular problem.

I am trying very hard not to focus on a "solution" at this 
point in time, but rather to discuss how other people have 
done this, NOT including issues like "make them separate 
records" which as we know from the above is simply 
irrelevant to the basic locking issue.

That said, I need suggestions for solving the real core 
issue, while still creating the appearance of creating new 
records AND editing existing records.  I have "narrowed" the 
  conversation down to replacing a bound form (which 
immediately places one of those accursed locks) with an 
unbound form which will in fact creates locks as well, but 
only for a few milliseconds at the time of the write back to 
the database.

In the end:

1) I need to record this data in a table.
2) These folks have a requirement that any given "contact" 
can be a small novel if they so desire.
3) They DEMAND that the data be written in chunks, with the 
aforementioned time and person stamps, said chunks often get 
to be page sized and more.
4) Given that (and staying within the MDB database model) 
the data has to go in a memo field.
5) Memo fields cause undesirable page locks for as long as 
the edit occurs.

ergo... unbind the data, allow the edits in an unbound 
control, then write it back in one quick write operation 
with only a very brief (instantaneous) write lock.

To be honest, the literal simultaneous edit of the same 
field is highly unlikely, while at the same time not 
impossible, particularly given the "gone to lunch" scenario 
and the process "bursting" phenomenon.

Additionally, given all of the "record locked by another 
user" messages they get now, a very occasional "record 
locked by User XXX" message would be way preferred.  Many 
generic locks would be replaced by a few educational locks.

I am leaning toward a "user lock" field where the literal 
name of the user who gets the record first is written to the 
field.  Since all writes to these records go through this 
one single form (ignoring any "sideways access" - I love 
that term ;-), it will be simple to go check the "user lock 
field" as the edit form opens on a given record, and advise 
the second and subsequent person trying to edit that this 
specific record is locked (and by who).  I can write code 
that clears the lock field in the form close event of the 
form that wins the edit request.  If the form closes, the 
lock WILL be cleared.

The form being opened by the second and subsequent person 
would have to poll the Lock Person field in the record and 
wait for that field to clear before trying to edit that 
record.  If I implement a variable time to check, that would 
help to ameliorate the "two people retrying at the same 
time" issue.

All of that sounds like the beginning of a solution.

Now... where are all the class writing UHUers when you need 
them eh?

John W. Colby
www.ColbyConsulting.com



More information about the AccessD mailing list