Drew Wutka
DWUTKA at Marlow.com
Wed May 28 14:28:15 CDT 2008
Calm down amigo! I have been listening. I know you aren't going to experience the same problems with a SQL Server backend. Before you get too irrated, please understand that I DO create unbound forms (though as I have stated, it's rarer these days, since I have found interfaces that have better advantages over Access). So my advice is valid. I understand your customer wants a specific table design, but my point is that the problem lies right there, in the table design. I am not mocking your abilities; I know you are a damn good programmer and database developer. What I have explained (though I may have gone off on a tangent a bit, sorry, we haven't argued in a while...maybe I needed a fix... ;) ), is that 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. 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. A single record in a table will only occupy a single page, unless you have a memo field, that's when things can get tangled. So, you have the following possible solutions: #1. Use something other then an .mdb, such as SQL Server. (discussed already) #2. Live with the problem and stick with an .mdb. (Not much of a solution) #3. Change the design so that a truly simple unbound solution would solve the issue, and allow the system to stay in an .mdb. (Your customer wants to keep the current design) #4. Create an unbound system using the existing data structure and GUI processes. If you are trying to save the data in a field that another user has already gone and changed in the background, you have the following options: A. Alert the user that changes were made to the data they are trying to save, and give them the option of dropping their changes and loading the new data, or dropping the other person's changes and saving theirs. (Not a very good option) B. Lock the record so that a change can't be made at the same time to the same record. (which is what a bound form already does). C. Try to create some whammadyne logic that will try to 'merge' the data between two different strings. If you are talking about a setup where the initial data is A, then B is added. And you have markers you can distinguish where A stops and B starts, and someone creates C, while another person is working on D, when D saves, you could find the start of D, and put it at the end of C, and then make sure that A and B are the same, then write the whole sequence of ABCD to the db. But this would require that no one could mess with the markers, and that you don't have C writer and D writer both changing B, because then you fall into option A again, because you'd have three representations of B (original, C modified and D modified). The problem with ANY option in #4 is the EXACT same problem that you are looking for a solution for. Ignoring the simultaneous entry issue, the MAIN problem is the USER DELAY issue. Someone starting to edit a record then going to lunch. The EXACT SAME PROBLEM exists with any implementation of #4. If your logic runs through and prompts your user (because of a change made by another user), if the user doesn't IMMEDIATELY reply to the prompt, it will allow another user to change the data in the background again, so before saving the data AFTER the user replies to the prompt, you have to check the data AGAIN....which can turn into either an endless loop, or eventually running into the possibility of LOSING DATA. You will never find an automated routine that will compare two strings, effectively merge the differences, and save the new data if you are allowing the users to edit anything within the string. It's just that simple. So, 1 and 3 are the ONLY viable solutions, 2 is no solution at all, and 4 is an ineffective solution. My other comments have been in jest John, but before you get on your high horse again, keep in mind that you asked an unbound question and an unbound expert replied with a response based on logic and expertise. I've been building systems for a few months myself you know.... Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, May 28, 2008 1:34 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] VBA Unbound data entry / update form Drew Wutka wrote: > Well, we certainly are going around in a circle here, that's for sure.... Drew, sometimes you are just an idiot (sorry to be so blunt). You are not even listening to the discussion. The crux of the matter is that Access and JET store memos and indexes in pages and lock the ENTIRE DAMNED PAGE of memo or index when a write lock is encountered for ANY MEMO OR INDEX stored in that page. I HAVE SAID THIS NUMEROUS TIMES! This affects records completely unrelated to the record being edited. This is a KNOWN issue with Access / MDB / JET data stores. THAT is the issue. I HAVE SAID THIS NUMEROUS TIMES! YOU ARE NOT LISTENING! I am having locking issues writing BRAND NEW RECORDS because too many people are in the same table at the same time, which apparently triggers this "locking issue". Editing existing records is JUST and ONLY a requirement that I have to deal with, it is NOT the primary issue. YOU ARE NOT LISTENING! NOW, you can call that a bound form issue if you will, the the plain and simple fact is that it is NOT an issue for bound forms which use SQL Server as the BE, simply because SQL Server does not handle memos and indexes (and locks) in this manner. So it is not SIMPLY and ONLY a bound form issue. So, now is it a "bound form issue"? It is indeed a bound form issue BECAUSE it uses a JET / MDB data store, and the client is not ready to move to SQL Server (though that is coming). So Drew (and EVERYONE ELSE!!!), just STOP. I respect and admire you and your ability but you are not being helpful. I do not need all this crap about "new records" and "bad design" and all that. Go start your own thread and wave your arms and spout whatever you want, I truly don't care (and might even watch, listen, learn and participate), but get the hell out of MY thread so I can discuss what I need to solve the problem. I have had about 47 posts COMPLETELY IRRELEVANT to this thread, and I am getting irritated now. Believe it or not, I have been writing Access databases for several months now ... ;-) and pretty much know my way around, except... I am asking for advice about implementing a simple unbound form, which has the capability of displaying existing records or creating new ones, which correctly handles all the "stuff" of checking for edits of said existing records, WITHOUT placing ANY locks on the source table except for the brief instance when the writeback occurs. ANYTHING ELSE DOES NOT BELONG IN MY THREAD. Thanks, 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.