Error when clicking too fast

[Petr Danes]

Don't hold your breath. And don't believe your lyin' eyes. Access isn't waiting and therefore, you did, intentionally or not spawn an asynchronous process.

If it wasn't waiting, it would be doing the things that come immediately after submitting the query. It is not doing any of those things, but it does commence doing them as soon as this stupid error message gets dismissed. So I conclude that it is waiting, no matter what you think.

OK, I'll tell that to my clients (like American Express) ...

Humble bragging is not attractive, and not relevant to the question I asked. Batch processing is fine if you have that sort of workload. I do not, and none of that has anything to do with this discussion.

Apparently you've never heard of EDI.

Also not relevant to the discussion.

If I can get virtually instantaneous results from multi-million row tables, you really ought to not have a problem with 130,000. That is a very small number of rows. Have you looked at your table schema? Have you looked at the relationships and the indexes? For complicated joins, have you considered making Views and using the views instead?

The pure number of records in a database is far from the only factor in determining how fast a query responds, which you must know if you are actually doing any of this stuff. Some things respond very quickly, some take a while. It is not possible to optimize for everything at the same time. I have this setup optimized for the stuff that users do the most, and I get nearly instant response on that. Some things take longer, and it is those less common ones that will occasionally lag long enough for the user to get ahead of the app.

130K is not a very small number of rows. It is obviously nowhere near any capacity limits for a serious database engine, but it is large enough to give it some work to do, if the joins and search criteria are complex enough. Yes, I could probably add some indexes and persisted views, but I don't want to bog down the update process too much. Adding all that stuff makes searching faster, and everything else slower.

You may also tell me to just get a faster server, so I will attempt to forestall that by dealing with it now. The server is fast enough for the work it's doing, and I'm not going to try convincing management to shell out thousands for a faster box, ESPECIALLY when there is no guarantee that such an outlay will fix anything. It's not a server problem, it's an Access problem - it's intercepting clicks when it's supposed to be waiting.

And it very obviously IS waiting - as I have written several times, the code immediately after the query executes only AFTER the error message is dismissed. It is NOT spawning an asynchronous process and going on its way. When user do not click in this period, all works exactly as it should - as soon as the recordset is returned, Access continues by executing the very next statement, right on through to the end.

 

[Petr Danes]

In order to stop Access, the popup MUST be modal. It will prevent you from clicking anywhere except on the close button of the popup. That is along the lines of what I suggested in my first reply but it does require that you identify the potential problem queries.

That's how I understand modal forms as well. But if it stops all Access activity, how does Access then submit the query? All my modal forms work that way - everything stops until the user deals with the modal form. I pop up a modal form and the app will sit, waiting for the user to do something, rather than going to get the records the user asked for. Only after the user dismisses the modal form will Access continue, only then submitting the query and again, waiting for the results, which is what is causing the entire problem in the first place. I may be missing something, but I don't see how that solves the problem.

You also need to take a long, hard look at your schema and your queries if you are experiencing wait tim long enough for users to get antsy. Take them into SQL server and run them there so you can see the execution plan that gets generated. For two reports, the selection was con convoluted, I ended up having to create stored procedures to assemble the separate recordsets and then put them together. Essentially, I had to control how the query engine evaluated the query and impose my will on it because it was choosing poor execution plans. Since you are not using bound forms, who cares if the recordset is not updateable.

They're not exactly getting antsy, they're just a bit careless. It's not a delay where they get frustrated with a long wait time and start pounding on the keyboard, trying to get a response. In fact, it's sort of the opposite. Most of the app gives instant response, and they are used to that - click and it's done, immediately on to the next task, exactly as it should be. Computers should wait for users, not the other way around. But once in a while, something is not instant, and they have simply clicked on the next button in the process, oblivious to the fact that this time, the app was still doing something. That's what brings up the error message, and that is what I am trying to get rid of.

 

[ebs17]

Would it be conceivable to place a transparent form over your actual form?

 

[Petr Danes]

Would it be conceivable to place a transparent form over your actual form?

You mean like a mask, that would keep them from be able to touch anything, but would not mangle the display? That's an intriguing idea - thank you.

Never done anything like that, but it sounds very much worth trying. I'll give it a shot and see how it works.

 

[Petr Danes]

It doesn't stop activity in the Popup. It just blocks everything else.

Oh, right. I wasn't thinking clearly about that.

Which of course we had no way of knowing.

I think you might have known, since there was no mention of anything like that until you brought it up to brag about how you had large clients, while constantly insisting that I was doing something which I most definitely am not - spawning an asynchoronous process.

Well, batch processes can take a while. That's why we run them during off hours. My response was only because you turned up your nose at batch processing

I know full well how batch processes work. I started on them back in the days when programming meant punching holes in 80-column Hollerith cards. I also know that it is something to be avoided, unless you have volums of data that cannot be handled on the fly. Almost every normal interaction humans have with computers these days has gone away from batch in favor of real-time transactions. Including banks and other large institutions that you mention. Most of the time, when I do something in a bank that deals with another bank, it happens instantly - literally. I have been in one bank while on the phone with a teller in another bank, and as the local teller tells me he has done what I asked, the teller on the phone says, 'Okay, it's here.'

Batch processes are now used only when volumes are huge. I said from the start I was dealing with a real-time system, where responses are expected now, not after a pile of stuff runs through. I do not turn up my nose at batch processing - it's fine as a fallback when large volumes of data are being done and nobody needs the results quickly. But it's not what I am doing.

(the way you turn your nose up at bound forms)

Nor do I turn up my nose at bound forms. I have no idea where you are dredging up all these behaviors you are trying to attribute to me. I said they were fine, as long as you were doing simple stuff. What I'm doing is far too complicated for a bound form, and it would be foolish to try using one. The right tool for the job, whenever possible.

Maybe, instead of turning your nose up at the best features of the product you are using, you might want to learn how to use those features.

Again, you are imagining things. I know how to use bound forms, and I do use them when appropriate. How you can sit there with a straight face and tell me that I 'should' be using a bound form when you have no idea what I'm doing is a mystery to me. Is it that you do not know how to use an unbound form?

130,000 is a lot of records for a human to process but it is a trivial number for Jet and ACE and insignificant for something bigger like SQL Server. You should not be experiencing any slowness unless what you are doing is sub-optimal.

Sure - it's a trivial number, IF everything is set up optimally for a particular task. Once again, it is not possible to optimize for everything. I have optimized what I could for the most common tasks, and some of that comes at the expense of other tasks. But as I have repeated so many times already, this is not a problem with SQL Server. It is a problem that Access is not queueing mouse clicks, but breaking out of a place where it is supposed to be waiting for a recordset to materialize in order to throw this superfluous and meaningless error message.

If you know something about that, I'd be happy to hear it. But I'm getting a little tired of all the pointless tangents.

 

[The_Doc_Man]

So, which is it? Why does Access not queue clicks while this code waits for a response?

If your back-end SQL engine uses pass-thru queries or any other asynchronous execution option, your problem is events based on external actions. A CLICK EVENT (button, text box, check box, you name it) is essentially a subroutine call triggered by an external I/O interrupt from the input buttons of the mouse. I may have said that awkwardly but I will clarify: A click event is converted from a physical device interrupt to a synchronous, queued event call and is threaded into the sequence of event calls that would normally be associated with the form. I BELIEVE but cannot PROVE that a click event is threaded to the front of the event-pending queue. (Can't prove it because Microsoft's Access code is not open-source so can't see how they actually do it.)

By way of contrast, the actions that are caused by bound form status changes (OPEN, LOAD, CURRENT, AFTER UPDATE, etc.) are SYNCHRONOUS subroutine calls triggered when Access is performing those implied actions for you. However, Access treats them in a very similar fashion. Every action has a "hook" in the form of an event routine entry point that you can declare to insert your own code into the sequence. Every event is NOT called immediately on execution of the related action. What really happens is that the front event of the event queue is executed next. That is, all events are linearized. From this discussion, it should be obvious that one event cannot interrupt another event, because they use a queue structure to keep track of what they are going to do.

By using unbound forms, you make the form's status-change events short-lived, which means you return from YOUR event code to the Access GUI code (which is invisible to you). Those button clicks CANNOT interrupt a running event routine but if you are just sitting at a screen where you have launched a back-end async process and have then returned from that process launch, you are not running event code. (Access is running GUI code.) So clicking the button will immediately queue up an event as described earlier, and because no event code is blocking it, those pesky little button clicks will make it through.

It's all fine to tell me it's my own fault, but it's not very helpful.

Agreed. So I have explained exactly WHY you are seeing this behavior. Your solution is to find a way to continue running event code while waiting for the process to complete. Just because you have launched a back-end query on a "smart" SQL engine doesn't mean you have to return from the routine that launched it. What you want to do during that time, I cannot say because that would be your choice. But the solution to those unwanted button-click executions is to run event code, because one event cannot interrupt another event.

EDIT: If you have two forms open at once, neither of which is bound, it is NOT CLEAR whether you can run processes under both forms. However, if only a single form is active, then an event on that form cannot interrupt another event on the same form, and that is because Access DOES NOT USE the API call that creates what is called a "FORK" process (parallel execution thread).

Note that for the purposes of this discussion, a TRAP handler is NOT event code even though you define it in what appears to be event context. A trap CAN interrupt event code (but does not interrupt another TRAP routine). I include the note only to forestall questions about interrupts.

 

[Petr Danes]

You have some really weird ideas.

Many things seem weird to people who don't understand them. But that's okay, I'm not trying to educate the world, just solve my own little problem.

It is what it is.

I don't know how to respond to meaningless tautologies.

Any process that doesn't require constant human interaction is by definition batch.

And I have said from the start that I do not have such a process, unless you consider a submitting a query to be a batch process, because the SQL Server engine is retreiving records without 'constant human interaction'. But if you do that, you could consider every single statement in any language to be a batch process, because the language execution engine performs that statement without such interaction. At that point, the term batch process loses all meaning.

You can't just arbitrarily put something in one basket or another.

No, and I'm not trying to. But it seems to me that you are.

And when someone complains about a long running process it is generally because they are trying to have a human interact with a batch process so no, I didn't not have any way of knowing that you were not making that mistake.

Except that I have stated repeatedly that I am not doing that. Yet you keep harping on it, just like you did about the non-existent asynchronous process you claimed I was spawning. I don't know what more to do, when I continuously write one thing and you keep claiming something else. I'm trying to solve a problem, not get involved in an argument.

If you don't like the popup solution, I'm not sure what else I can suggest.

I do like that, and I said so - I'm going to try it and see how it works. It seems a good trick, although I think it should not be necessary - Access should do what it's told and wait, queueing up mouse click and dealing with them when appropriate, without going off and generating spurious error messages about something that isn't even happening. But it's far from the only screwy thing that Access does. If this solves the problem, I will be reasonably content.

 

[Petr Danes]

II BELIEVE but cannot PROVE that a click event is threaded to the front of the event-pending queue. (Can't prove it because Microsoft's Access code is not open-source so can't see how they actually do it.)

That would make sense as to what is happening. I don't think it's the full explanation, because for instance, you can't interrupt a stuck loop with a mouse click on the VBA Stop execution button, unless you have a DoEvents line somewhere in the loop. So some mouse clicks get ignored, although there is no wait in such a code loop, as there is when submitting a pass-through query. Maybe that's the difference.

By way of contrast, the actions that are caused by bound form status changes (OPEN, LOAD, CURRENT, AFTER UPDATE, etc.) are SYNCHRONOUS subroutine calls triggered when Access is performing those implied actions for you. However, Access treats them in a very similar fashion. Every action has a "hook" in the form of an event routine entry point that you can declare to insert your own code into the sequence. Every event is NOT called immediately on execution of the related action. What really happens is that the front event of the event queue is executed next. That is, all events are linearized. From this discussion, it should be obvious that one event cannot interrupt another event, because they use a queue structure to keep track of what they are going to do.

By using unbound forms, you make the form's status-change events short-lived, which means you return from YOUR event code to the Access GUI code (which is invisible to you). Those button clicks CANNOT interrupt a running event routine but if you are just sitting at a screen where you have launched a back-end async process and have then returned from that process launch, you are not running event code. (Access is running GUI code.) So clicking the button will immediately queue up an event as described earlier, and because no event code is blocking it, those pesky little button clicks will make it through.

Hm, that makes sense. I have only recently started playing with custom event code. Maybe if I had more ecperience with that, it would be clearer in my mind how this all stacks together.

But submitting a pass-through query and waiting for the results isn't really returning from that process, is it? Yes, Access is not directly involved in what SQL Server engine is doing, but when I specify 'Returns records' in the query, and give it a timeout value, I am telling it to do something - something very specific: "Send off this command and wait for the results, bringing me back the results when you get them." It does not return from that command until it has the records. And even when the user clicks something that generates this error message, when the Ok button is clicked, it continues right there where it was, having by then received the requested records.

Agreed. So I have explained exactly WHY you are seeing this behavior. Your solution is to find a way to continue running event code while waiting for the process to complete. Just because you have launched a back-end query on a "smart" SQL engine doesn't mean you have to return from the routine that launched it. What you want to do during that time, I cannot say because that would be your choice. But the solution to those unwanted button-click executions is to run event code, because one event cannot interrupt another event.

Do you mean if I launched a custom event to do the SQL Server call, and had THAT code waiting for the response, my code would then be immune to user clicks? That also sounds like an interesting way to do it, and not one that would have occurred to me. It might even look cleaner to the user than a pop-up form. I'll try both and see what l like better.

EDIT: If you have two forms open at once, neither of which is bound, it is NOT CLEAR whether you can run processes under both forms. However, if only a single form is active, then an event on that form cannot interrupt another event on the same form, and that is because Access DOES NOT USE the API call that creates what is called a "FORK" process (parallel execution thread).

You've lost me here. I know I can have several such forms open at once, and all can have code in them, but Access is single-threaded (as far as I know), so I can only ever have one active at a time. Maybe I am not understanding what you mean here (entirely possible, I am far from an expert on Access internals), but it seems to me you can have processes under as many forms as you have open, just not executing simultaneously. Or am I missing your point completely?

Note that for the purposes of this discussion, a TRAP handler is NOT event code even though you define it in what appears to be event context. A trap CAN interrupt event code (but does not interrupt another TRAP routine). I include the note only to forestall questions about interrupts.

That's a little over my head. Sorry, but I don't even know what a TRAP routine is, unless I know it by another name and am not aware of that.

 

[arnelgp]

When the users click a button

Is the button on same form? can you Disable all buttons until the process is completed?

 

[Petr Danes]

Is the button on same form? can you Disable all buttons until the process is completed?

I could, but there are MANY buttons on the form, as well as a bound subform, also with buttons. Constantly disabling and re-enabling everything would be a monumental PIA, as well as probably making the form flicker a lot. I'm trying to avoid that.

 

[arnelgp]

you can create a sub/function to Disable/Enable all command buttons:

Public Sub EnableDisableButtonsOnForm(ByRef frm As Form, ByVal Enable As Boolean)
' arnelgp
'
' purpose:
'   Enable/Disable command buttons
'
' note:
' to disable, passed False to Enable parameter, otherwise pass True value
'
    Dim ctl As Control
    For Each ctl In frm.Controls
        With ctl
            If TypeOf ctl Is CommandButton Then
                .Enabled = Enable
            Else
                If TypeOf ctl Is SubForm Then
                    Call EnableDisableButtonsOnForm(ctl.Form, Enable)
                End If
            End If
        End With
    Next
End Sub

to use:

'disable all buttons in the form and subforms
Call EnableDisableButtonsOnForm(Me, False)
' some query or process here
'
'
'enable them again when done processing
Call EnableDisableButtonsOnForm(Me, True)
'

 

[arnelgp]

probably making the form flicker a lot

you can try it, if it flicker then just forget the suggestion.
on my test db, flicker did not happen.

 

[Petr Danes]

you can create a sub/function to Disable/Enable all command buttons:

Public Sub EnableDisableButtonsOnForm(ByRef frm As Form, ByVal Enable As Boolean)
' arnelgp
'
' purpose:
'   Enable/Disable command buttons
'
' note:
' to disable, passed False to Enable parameter, otherwise pass True value
'
    Dim ctl As Control
    For Each ctl In frm.Controls
        With ctl
            If TypeOf ctl Is CommandButton Then
                .Enabled = Enable
            Else
                If TypeOf ctl Is SubForm Then
                    Call EnableDisableButtonsOnForm(ctl.Form, Enable)
                End If
            End If
        End With
    Next
End Sub

to use:

'disable all buttons in the form and subforms
Call EnableDisableButtonsOnForm(Me, False)
' some query or process here
'
'
'enable them again when done processing
Call EnableDisableButtonsOnForm(Me, True)
'

Thanks - yes, that would work. I would have to make it a bit more complicated, because I have toggles, embedded subform and other things on the form, and some of them are already disabled for various reasons, so I would have to keep track of every control's status before calling this routine and put back only the ones that were supposed to be enabled. But the principle is sound - if I can't get anything more elegant to work, I'll give this a try.

 

[Petr Danes]

you can try it, if it flicker then just forget the suggestion.
on my test db, flicker did not happen.

And now that I think about it, I wonder if I could just disable the entire form, and that would cover everything in one command. I'm not at work for a few days now, but I have several things to test when I get back. This is going to be interesting.

 

[The_Doc_Man]

you can't interrupt a stuck loop with a mouse click on the VBA Stop execution button, unless you have a DoEvents line somewhere in the loop. So some mouse clicks get ignored, although there is no wait in such a code loop

Or if you are in a loop, you MUST be in an event that another event cannot interrupt (unless you have a DoEvents somewhere). So if you eventually get in something to stop the code, your mouse clicks in that context aren't ignored; they are flushed. A "stop code" isn't selective. It isn't "stop one." It is "stop all."

But submitting a pass-through query and waiting for the results isn't really returning from that process, is it?

Having pointed out that Access code isn't open-sourced, I can't answer that from certain knowledge. However, from the observed behavior in question, if you CAN click a button and have it respond immediately, I would say you must have returned from the event.

Do you mean if I launched a custom event to do the SQL Server call, and had THAT code waiting for the response, my code would then be immune to user clicks?

I think that is the case. Your question about getting stuck in a loop suggests exactly that behavior.

it seems to me you can have processes under as many forms as you have open, just not executing simultaneously. Or am I missing your point completely?

Missing the point. They won't be executing simultaneously - but there is only one event queue and if you have more than one event source, you won't be easily able to predict which event comes next. Further, most event routines are relatively short. Therefore if two forms are going through state changes at once, their events might intertwine. For instance, launch two forms (not sub-forms of a main, but two independent forms in parallel). They will both open. Once they open, they are on their own (since they aren't sub-forms.) They can have OPEN, LOAD, CURRENT, and other events depending on what they do. When an event routine executes that EXIT SUB (or the implied EXIT SUB found in an END SUB), the form is doing the same thing more or less as a DoEvents would do. Which means if the other form still has events pending, it's off to the races. They won't run in parallel, but then if you think about time-sliced time-sharing, that doesn't run things in parallel either - but it sure seems like it does.

I don't even know what a TRAP routine is, unless I know it by another name and am not aware of that.

Try "Error Handler." An error routine based on the ON ERROR THEN xxxx syntax represents an interrupt. When you get a "memory usage violation" or an "out of memory" error or an "arithmetic overflow" error, those are often detected in the hardware via a mechanism called a TRAP, which is like an interrupt in some cases but not in other cases. Traps also use a queue but that queue is separate from event queues. Traps fly by very different rules. I mentioned it as a precaution to clarify that when we are talking about "one event can't interrupt another event" that in fact an event routine CAN be interrupted by something else - in this specific discussion, a trap sequence.

 

[MajP]

Stupid question here, but why not a simple pop up progress meter that is modal dialog? I would think that is kind of the standard way to do this.

 

[Minty]

I don't think you can, without running the query from the modal form, as code execution in the calling form is halted until the modal form is closed.

 

[Petr Danes]

Stupid question here, but why not a simple pop up progress meter that is modal dialog? I would think that is kind of the standard way to do this.

Not at all a stupid question. That would work to block clicks, but the problem with a progress meter is that I have no way of knowing how far along SQL Server is in the job. These are pass-through queries - I build them, submit them and wait for the results to come back. They generally come back within at most a second or two, usually much quicker, which is how the users have gotten in the habit of not waiting, and just clicking the next thing they want. I have a simple background color change from green to yellow as a 'caution light' to indicate that something is going on, and back to green when it's done, but as usual, people eventually get used to how things work and completely lose awareness of the visual signal. They are used to being able to click on what they want, when they want, and the occasional instance when it lags is infrequent enough that the color change simply doesn't register.

And of course, it also varies by user. Some are more careful, some are headlong in their work and pay little attention to anything else. It does no good to chastise them, and that's not how I see my job anyway. My task is to make the software work how they need it to work, and that includes making it immune to their screwups, as much as I possibly can.

I do use progress meters when my code is doing some more extended operations, and that works nicely, but I can't use it to show progress on a simple query that is only a single statement - QueryDef("xxx").OpenRecordset.

 

[Petr Danes]

I don't think you can, without running the query from the modal form, as code execution in the calling form is halted until the modal form is closed.

Not quite - as Pat pointed out a few posts back (and I had spaced over when I wrote my own objection to it), a modal form halts all execution OTHER THAN in the modal form. The modal form blocks all other user activity, but its own code continues to run. It can make the query call, and wait without fear of being interrupted, then close itself when the recordset materializes, all without any user interaction. Of course, if the query hangs or something else goes wrong inside, you have no way to get control back and you have to kill Access .

 

[The_Doc_Man]

These are pass-through queries - I build them, submit them and wait for the results to come back.

This is essentially asynchronous. You trigger the query but then have to wait for it. The catch is, you don't HAVE to wait because once the query has been sent to the back-end, you are DONE. When you were denying earlier that you had done anything asynchronously, you were glossing over this little fact. A pass-thru query is handled by the BE server REGARDLESS of what you do next.

Using the ACE database engine, the "handshake" keeps everything synchronous because (as I understand it) the handshake between client and server involves a protocol that is of the "prompt/response" type (some call it "challenge/response") like working with a serial terminal interacting with a command-line based server. You get nothing from the server until you type something to trigger it.

But with a pass-thru query, that query is now an independent process. If you consider what you do in the front-end AFTER the query has been submitted, there is where you would put something to keep the event code in play. And if you don't, if you reach an EXIT SUB or END SUB, your code just became asynchronous (with respect to the overall process that is now running on TWO machines - the client and the server.)