Drew Wutka
DWUTKA at Marlow.com
Wed May 28 17:25:56 CDT 2008
Question, are the people entering this data ever actually EDITING information? That is the real question. Are they ever going back into a SAVED record and changing data that was already entered. I'm not talking about changing a record, I'm talking about changing the data that already exists within the record? You can do this VERY easily without ANY 'data checking' necessary if you split these entries up into their own records, with an unbound form. You can also make the form LOOK like they are adding the data to the same 'record'. You can even go as far as creating a query that will display all of this data in one field, in case they have some bazaar reason for wanting it all in one physical field (there is no logical difference between a table and query...). And MOST importantly, it can all be done without ever having a user run into a record lock. All of this can be done in a very brief amount of time. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, May 28, 2008 3:28 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] VBA Unbound data entry / update form 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.