[AccessD] VBA Unbound data entry / update form

Drew,

Thanks for that lucid discussion of the issues.

8-)

Believe me, I do not want to get on any high horse, I just 
want to discuss the alternatives to bound.  I do NOT 
understand fully the implications of any particular method 
of unbound data entry exactly because I do not use it if I 
can avoid it, for obvious reasons.

To be honest, given the folks on this list, given the heavy 
use of classes amongst our list members, and given the 
strong opinions about unbound being "the way to go" by a 
certain subset of our members, I fully expected some healthy 
discussion about generic "here's how to do it, with this 
class to do this part and this class to do this other part 
etc.  It is a problem that certainly seems to beg for a 
class based solution.

The entire thread just seemed to get dragged down in stuff 
entirely unrelated to handling the locking issues that you 
just referred to.

My apologies to everyone if I came across harshly, I am just 
trying to get my thread back on track.

John W. Colby
www.ColbyConsulting.com


Drew Wutka wrote:
> 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
> 
> 
>