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