Error when clicking too fast (1 Viewer)

[ebs17]

Have you implemented suggestions like temporarily covering the user interface, or are you still considering (suggestion and thinking about it for free)?
Enough contributions have already been made for a purely academic discussion.

 

[pdanes]

That's what DoEvents does.
That while wend loop I showed would enable you to open another form within access, or do other things, so the app remains responsive for users.

DoEvents releases the processor to allow OTHER tasks to run. It does not allow Access to start another internal thread. I do not think Access has the ability to do this at all.

I thought you said you changed the form background colour to signal the process had completed.

Normally the background is green. I change it to yellow just before initiating communication with the server, and change it back to green when the last call has responded (some actions by the user require several calls to the server).

You also showed you changed all the edit flags. I just thought you didn't need to change all the edit flags to achieve what you wanted, and you could do this by testing the colours.

Testing the color is not possible. My code is stalled, waiting for the server to begin delivering records. And even if I could test it, all it would tell me is that I am waiting for the server's response, which I already know.

You may be right though. I have a DBS where I think an issue is caused by users processing ahead and not waiting until a particular process is complete. Occasionally the active record changes before the process completes. I think it's caused by the asynchronous nature of certain tasks, and offered to build in a wait, but the client doesn't want me to bother at the moment.

It's probably also the difference between a developer who knows not to press keys until appropriate, and a user who has a lot of work to do, and is frustrated when an app appears unresponsive, and keeps pressing keys to try to get a response.

This isn't quite that. Normal response time is instant, and the users are accustomed to that. This is not a case of beating on an apparently unresponsive app - they are used to being able to do whatever they want, whenever they want, and the occasional instance when the server takes a second or two to respond is just enough time for them to click something else, having not noticed that it is still yellow.

Access should either ignore or queue up mouse clicks when it is waiting for something, not respond in real time by throwing a meaningless and untrappable error.

 

[gemma-the-husky]

The mechanism that I actually had in mind was to

1) open a form saying "please wait for server to respond"
2) having the dbs close that form at the appropriate time, ie after the server responded.

I need a doevents for that

while pauseform is open
doevents
wend


I added this to one part of my code where it would be harmless (a simple read data request), and noted a flicker as the warning form opened, and then disappeared almost instantly. I didn't add it to the place where it was really needed though, where the user submitted a write data request, but then possibly wasn't waiting for a response before continuing. My programme didn't wait for the write to complete. I didn't think it was actually that easy to detect that the write had completed (without some careful testing).

I don't think it's necessarily as easy (simplistic) as you think. What if the asynchronous event never responds at all because of some error? How would you deal with that? Try changing your programme so that the process you call just pauses. See how your dbs works in that case. Can you do anything or not in that case? I don't know the answer, I'm just thinking out loud.

 

[pdanes]

Have you implemented suggestions like temporarily covering the user interface, or are you still considering (suggestion and thinking about it for free)?
Enough contributions have already been made for a purely academic discussion.

See #66.

 

[pdanes]

The mechanism that I actually had in mind was to

1) open a form saying "please wait for server to respond"
2) having the dbs close that form at the appropriate time, ie after the server responded.

I need a doevents for that

while pauseform is open
doevents
wend

That's what the yellow color was supposed to do, and what my added disabled events do now. But a DoEvents has nothing to do with that. When I issue an OpenRecordset command, the VBA routine issuing that command STOPS AND WAITS until the server responds. Even if I had the DoEvents immediately after the OpenRecordset command, that DoEvents command will not be executed until the server has replied with a recordset. At that point, the DoEvents will do nothing. Before that, it will also do nothing. DoEvents simply has no effect or bearing on this issue. Opening another form will not affect that. As long as this While loop is running, nothing in that other form will execute. A loop like this will do nothing except make Access run in circles. It will never stop, because the form will never close.

I added this to one part of my code where it would be harmless (a simple read data request), and noted a flicker as the warning form opened, and then disappeared almost instantly. I didn't add it to the place where it was really needed though, where the user submitted a write data request, but then possibly wasn't waiting for a response before continuing. My programme didn't wait for the write to complete. I didn't think it was actually that easy to detect that the write had completed (without some careful testing).

The server tells you when the write has completed. You issue the write command, and Access waits for an acknowledgement before continuing. The server does not issue that acknowledgement until it has successfully completed the write.

I don't think it's necessarily as easy (simplistic) as you think. What if the asynchronous event never responds at all because of some error? How would you deal with that? Try changing your programme so that the process you call just pauses. See how your dbs works in that case. Can you do anything or not in that case? I don't know the answer, I'm just thinking out loud.

If an asynchronous command is issued, then it is up to the application how long it wants to look for some verification, and what to do when it either gets it or some time has elapsed without getting it. But I'm not doing that. I have a SYNCHRONOUS set of processes. Access issues a request and WAITS, until SQL Server starts returning records. When that happens, the next VBA statement in line after the OpenRecordset is executed. I have written this so many times during the course of this thread that I am starting to go cross-eyed.

OpenRecordset waits for a response.
OpenRecordset waits for a response.
OpenRecordset waits for a response.

I do not know how to make it any clearer.

 

[gemma-the-husky]

Well can you use the form.keypress event to eat keystrokes until you are ready for them?

 

[The_Doc_Man]

Access should either ignore or queue up mouse clicks when it is waiting for something, not respond in real time by throwing a meaningless and untrappable error.

The fact that it respond to the mouse clicks even though your data set is not ready verifies that you have an asynchronous process. Access DOES queue up its mouse clicks until it regains execution control. The fact that it processes the clicks before you were actually ready is indicative of a design mismatch between what was desired and what actually happens. You are trying to force Access to conform to your mind-set. Square peg, round hole.

There is no such thing as not having the option of waiting.

Don'tcha just love double negatives? Let me double-un-negate that sentence. It is true that you DO have the option of waiting, but you keep on asking the wrong question. The REAL question is, if you decided to NOT wait, does it make any difference? If you don't wait and something happens, you were dealing with an asynchronous process. If it doesn't matter that you chose to not wait because nothing happens anyway, then you were dealing with synchronous processes that will catch up with you once they synchronize.

It does not allow Access to start another internal thread. I do not think Access has the ability to do this at all.

Technically, it is possible. We had someone who used Windows API calls to activate multiple threads to use external WinSock routines. It was a nightmare worse than putting badgers in a burlap sack and swatting them with a wooden slat. However, if you aren't playing with the API libraries, you are correct. Access cannot operate asynchronously. HOWEVER, there is a loophole.

When Access is talking to either JET or ACE, the old or new Access SQL engine, those tasks are ALSO synchronously coded AND they communicate with the front-end over synchronous I/O channels. That is because when Access was first built, there was no such thing as a multiprocessor PC. Nobody built a process fork/rejoin operation because the CPU wasn't going to timeslice anyway. The first versions of Access ran in the Windows environment, but that was a layered GUI app running on top of MS-DOS, which was single-threaded. Besides that, using native Access back-ends, it is the processor on the front-end that is actually doing the work anyway.

When you talk to any SQL-based but independent server (SQL Server, My SQL, ORACLE, SYBASE, etc.), those ARE written to allow parallel operation of multiple queries. (Actually, not going to swear that My SQL does that, but the others ABSOLUTELY do.) You CAN give any of those DB systems a query and maintain a connection that you could monitor while the query progresses. We've had many threads on this forum that actually do that. The loophole I mentioned earlier works because the processor on the back-end machine neither knows nor cares about what the processor is doing on the front-end machine. There are two independent and grudgingly cooperative processes at work.

 

[pdanes]

The fact that it respond to the mouse clicks even though your data set is not ready verifies that you have an asynchronous process. Access DOES queue up its mouse clicks until it regains execution control. The fact that it processes the clicks before you were actually ready is indicative of a design mismatch between what was desired and what actually happens. You are trying to force Access to conform to your mind-set. Square peg, round hole.

Then why does it not respond to them once processing resumes AFTER the SQL request? It's not queueing them up, and I don't have an asynchronous process. I have repeated this until I am blue in the face. This is something Access is doing on the side, on its own. It does NOT process the clicks, except to display this spurious message, and it does not respond to the click once the recordset has begun filling. The click is swallowed and execution remains halted at my SQL request until the server begins responding with records. That construct of waiting for the other side to respond is what makes it a synchronous process - each side issues some sort of communication and does not do anything else in that thread until the other side responds.

Don'tcha just love double negatives? Let me double-un-negate that sentence. It is true that you DO have the option of waiting, but you keep on asking the wrong question. The REAL question is, if you decided to NOT wait, does it make any difference? If you don't wait and something happens, you were dealing with an asynchronous process. If it doesn't matter that you chose to not wait because nothing happens anyway, then you were dealing with synchronous processes that will catch up with you once they synchronize.

There is nothing wrong with two negatives in a sentence, but my sentence is not a true double negative in the sense of an awkward construction, like , "I'm not going nowhere." But if you prefer to reword it, fine.

In any case, what you write here makes no sense to me. How would I decide NOT to wait, and why would I want to do that? I'm issuing a request for records. What would I do if I decided to not wait? Go play pinochle while the server gathers the records? It's ridiculous. I'm telling other process to go get something for me, and I wait until it does so. That is what defines synchronous processes - they communicate with each other and each waits for the other's response. If I decided not to wait, hell yes it would make a difference. My very next task is to display the records the user requested. What would you have me do - go to all sorts of effort to briefly display a message saying that the server is processing the request? Then write even more pointless code to run in circles constantly testing to see if the server has begun to respond yet? Most of the time it DOES respond instantly - this would be great deal of effort for absolutely no gain, and would add another pile of error-prone code.

"If it doesn't matter that you chose to not wait because nothing happens anyway" - I have no idea what you mean by this. The code DOES choose to wait, and things ARE happening - records are being retrieved and delivered back to my app.

Technically, it is possible. We had someone who used Windows API calls to activate multiple threads to use external WinSock routines. It was a nightmare worse than putting badgers in a burlap sack and swatting them with a wooden slat. However, if you aren't playing with the API libraries, you are correct. Access cannot operate asynchronously. HOWEVER, there is a loophole.

When Access is talking to either JET or ACE, the old or new Access SQL engine, those tasks are ALSO synchronously coded AND they communicate with the front-end over synchronous I/O channels. That is because when Access was first built, there was no such thing as a multiprocessor PC. Nobody built a process fork/rejoin operation because the CPU wasn't going to timeslice anyway. The first versions of Access ran in the Windows environment, but that was a layered GUI app running on top of MS-DOS, which was single-threaded. Besides that, using native Access back-ends, it is the processor on the front-end that is actually doing the work anyway.

When you talk to any SQL-based but independent server (SQL Server, My SQL, ORACLE, SYBASE, etc.), those ARE written to allow parallel operation of multiple queries. (Actually, not going to swear that My SQL does that, but the others ABSOLUTELY do.) You CAN give any of those DB systems a query and maintain a connection that you could monitor while the query progresses. We've had many threads on this forum that actually do that. The loophole I mentioned earlier works because the processor on the back-end machine neither knows nor cares about what the processor is doing on the front-end machine. There are two independent and grudgingly cooperative processes at work.

I don't know what grudgingly cooperative processes are. All that I have seen either work together or they don't. These do.

I don't doubt that Access can be tortured beyond its design limits to do things the designers never intended, but I'm not doing anything like that.

Both Jet and external engines run in this 'semi-asynchronous' manner, where they start returning records and the VBA code may continue as soon as the first is retrieved, instead of waiting for them all. But that has nothing to do with all this.

 

[aawetu]

Bound forms are fine when doing simple things. This app is decidedly NOT simple, and many of its functions would be completely impossible with bound forms. Some can be bound, and are. The more complex ones are out of the question.

 

[The_Doc_Man]

Both Jet and external engines run in this 'semi-asynchronous' manner, where they start returning records and the VBA code may continue as soon as the first is retrieved, instead of waiting for them all. But that has nothing to do with all this.

Not a true statement. The "Both Jet and ..." part is wrong. Jet, like Ace, operates synchronously to build a result-set. In external SQL engines, the result set is built locally, asynchronously with the FE, and then transferred to the FE via the recordset object. In Jet and Ace, the result set is in the file that Jet or Ace can directly access to build the fully-extant temp table that contains the list of returned items.

OK, a qualifier on that statement. That is the way that Microsoft documentation describes it when discussing pass-thru and non-pass-thru queries. But since Access is NOT open-source, we have a hard time verifying the details.

EDIT: A further wrinkle is that a poorly designed query that includes Access VBA functions might force Access to do the processing rather than the back-end engine. Whenever THAT happens, you can bet that everything becomes synchronous.

 

[gemma-the-husky]

Bound forms are fine when doing simple things. This app is decidedly NOT simple, and many of its functions would be completely impossible with bound forms. Some can be bound, and are. The more complex ones are out of the question.

I would be very surprised if you need unbound forms. I have only needed them a handful of times, and I suspect with more understanding I could have avoided the use of unbound forms completely.

 

[pdanes]

I would be very surprised if you need unbound forms. I have only needed them a handful of times, and I suspect with more understanding I could have avoided the use of unbound forms completely.

Then you are obviously doing far different sorts of work than what I'm doing. Bound forms are fine for simple manipulations of datasets, but there are a great many tasks that are not nearly so simple. Unbound forms are just another tool in the toolbox. They are good for some things, not good for other things, but in no way are they some sort of crutch that is to be avoided when possible.

If all your work is simple enough that bound forms are adequate, great - use them. Mine is not.

 

[pdanes]

The "Both Jet and ..." part is wrong. Jet, like Ace, operates synchronously to build a result-set.

It does NOT. Try it yourself - populate a large local table, then issue an OpenRecordset command and immediately test RecordCount. It will NOT show the full count. It will always be at least one, and in the background, Jet will continue filling in the records, EXACTLY as does SQL Server. If the app insists on seeing a record that has not yet been delivered, processing will STOP until that records is delivered, whether by Jet or SQL Server - a synchronous set of processes.

In external SQL engines, the result set is built locally, asynchronously with the FE, and then transferred to the FE via the recordset object. In Jet and Ace, the result set is in the file that Jet or Ace can directly access to build the fully-extant temp table that contains the list of returned items.

It would only be asynchronous if the issuing app continued on its way without waiting for the records. Mine does NOT, as I have written so many times. And if I manually insist on seeing the last record, or the full count, once again, the app WAITS until those counts or records are delivered by the engine, whether SQL Server or Jet or something else. SYNCHRONOUS.

EDIT: A further wrinkle is that a poorly designed query that includes Access VBA functions might force Access to do the processing rather than the back-end engine. Whenever THAT happens, you can bet that everything becomes synchronous.

Yes, but I don't do it that way. I don't know eveything there is to know about all this, but I do know better than that.

And it would not affect the issue of being synchronous. It would force Access to wait until the ENTIRE recordset had been delivered, just as it would now were I to issue a .MoveLast command, rather than permitting it to continue with the first one and filling in records in the background.

 

[The_Doc_Man]

Try it yourself - populate a large local table, then issue an OpenRecordset command and immediately test RecordCount. It will NOT show the full count.

Have, actually. You are looking at this incorrectly. The delay is due to the action of loading the finished recordset. It is that lengthy action that causes the delay that you see. In my genealogy database, which involves tens of thousands of records, I see that delay quite often, even if I am opening a big, static table that WASN'T created by a query. It is my main data-source table created from a data import-and-parse operation.

 

[The_Doc_Man]

And if I manually insist on seeing the last record, or the full count, once again, the app WAITS until those counts or records are delivered by the engine, whether SQL Server or Jet or something else. SYNCHRONOUS.

And yet if you do a database.Execute, the .RecordsAffected count is available right away after a successful execution under native Access. You keep on conflating the act of opening the recordset results with the act of executing the query. It is a DIVISIBLE ACTION.

 

[pdanes]

And yet if you do a database.Execute, the .RecordsAffected count is available right away after a successful execution under native Access. You keep on conflating the act of opening the recordset results with the act of executing the query. It is a DIVISIBLE ACTION.

The same is true of SQL Server. If I issue a command to execute a stored procedure, the result is available as soon as the procedure completes. That has nothing to do with opening a recordset.

 

[The_Doc_Man]

The same is true of SQL Server. If I issue a command to execute a stored procedure, the result is available as soon as the procedure completes. That has nothing to do with opening a recordset.

But the difference is that the procedure can theoretically complete while the FE does something else.

 

[pdanes]

But the difference is that the procedure can theoretically complete while the FE does something else.

Yes, theoretically it could. But the point is that it doesn't - it waits. In all these cases, Access waits for a response.

Sometimes the response is, "Here you go."
Sometimes the response is, "Here is the first record, I'm working on the others."

In both cases, Access waits for that response. Waiting for the other side to respond is what defines a synchronous set of processes. Having an option to do something else does not make it asynchronous, unless you avail yourself of that option.

 

[The_Doc_Man]

Having an option to do something else does not make it asynchronous, unless you avail yourself of that option.

We are arguing over semantics. Having the option to do something else INCLUDES the option to asynchronously wait and do nothing, because that, too, is a choice. If you were waiting and had a choice to do something else, it is asynchronous no matter WHAT you chose. If you were waiting because there WAS no choice, it is synchronous.

 

[pdanes]

We are arguing over semantics. Having the option to do something else INCLUDES the option to asynchronously wait and do nothing, because that, too, is a choice. If you were waiting and had a choice to do something else, it is asynchronous no matter WHAT you chose. If you were waiting because there WAS no choice, it is synchronous.

"Waiting and doing nothing..." Waiting for what? The sun to go down? If you are waiting, then you are waiting for the other process. THAT makes it synchronous. There is always the option to not wait, but if you need the results from the partner process, not waiting is silly. What would you do in the meantime?

I have the option to kill people. That does not make me a murderer. TAKING the option is what would make me so. Having it means nothing.