Error when clicking too fast

[Petr Danes]

SQL Server and Jet/Ace both respond with the first record. Agreed. But the question is whether at the time, the OTHER records were (Jet/Ace) or were not (SQL) available. Your freshly opened recordset ALWAYS gives you the first record for either case. That behavior of slowly filling the recordset even happens on my home system based on the Ace engine, which in my case is a monolithic database (since I don't share it.)

Um, yes - that's what I've been saying all along. What I got from you and others, like Pat Hartman, was that Access issues the OpenRecordset command and IMMEDIATELY continues, without waiting for any sort of response - an asynchronous process - a process spawned and left to its own devices, with no feedback to the originator. And I've been saying that's not true, because I know what commands come after the OpenRecordset, and those are not being executed until records start coming back. I'm certain I didn't say until ALL records are available, because I know that's not how it works - I know that to make things run faster, a response is generated as soon as at least one record becomes available, and filling of the recordset continues in the background. But I have been saying the whole time that Access WAITS for a response, and everyone has been telling me that it doesn't.

So now I don't know what to think. Have we been talking about two different things the whole time?

 

[ebs17]

When I want the query to be fully executed and the recordset to be fully populated, I do a MoveLast and overcome the lazy loading.

One could also think about ADODB: ExecuteComplete

 

[Petr Danes]

When I want the query to be fully executed and the recordset to be fully populated, I do a MoveLast and overcome the lazy loading.

One could also think about ADODB: ExecuteComplete

Yes, I MoveLast as well. I've used ADO very rarely - I remember it has that ability, but I don't recall ever using it. Most of my stuff is small enough that I don't need to stage stuff that way.

 

[The_Doc_Man]

So now I don't know what to think. Have we been talking about two different things the whole time?

Very likely yes.

Treat the execution of the query to form a recordset and the presentation of records following the OpenRecordset as two different but sequential actions. The fact that you can open the recordset means it exists - but in the SQL Server case, doesn't meant it is complete. It DOES mean that you can start feeding records from SQL to Access. As it happens, in the Jet/Ace case, it DOES mean the recordset is complete. Two different engines, two different details. However, BOTH engines follow (at least) the ANSI SQL 92 standard.

There is this little bug-a-boo called "implementation details" that describes things left to the implementer of the SQL package. The detail of what signals you get when something is finished are often left to the person(s) who wrote the underlying code.

 

[Pat Hartman]

Post #5 on Thursday at 10:38 AM. You might have tried the suggestion on ONE form. It isn't necessary to change every form to see if a suggestion works.

Hope you had a lovely holiday.

 

[Petr Danes]

I finally settled on a fairly simple adaptation. In the code that shows the warning yellow background, indicating that the app is waiting for records to start coming back from the server, I added the following:

With gbl_frmAkces
    .AllowEdits = False
    .AllowDeletions = False
    .AllowAdditions = False
End With

and to the code that returns the green background to indicate that the requested recordset has been successfully opened, I added:

With gbl_frmAkces
    .AllowEdits = True
    .AllowDeletions = True
    .AllowAdditions = True
End With

That (so far, anyway) seems to lock up the main form so that it ignores clicks while waiting for the server.

 

[The_Doc_Man]

I finally settled on a fairly simple adaptation. In the code that shows the warning yellow background, indicating that the app is waiting for records to start coming back from the server, I added the following:

With gbl_frmAkces
    .AllowEdits = False
    .AllowDeletions = False
    .AllowAdditions = False
End With

and to the code that returns the green background to indicate that the requested recordset has been successfully opened, I added:

With gbl_frmAkces
    .AllowEdits = True
    .AllowDeletions = True
    .AllowAdditions = True
End With

That (so far, anyway) seems to lock up the main form so that it ignores clicks while waiting for the server.

Not a bad idea. And you are right... when the .Allow options block an action, a click that would attempt a blocked action simply gets ignored. Yet if you had another form open, its .Allow options could be different, so your blockage is selective to the offending form. Good luck on the rest of your project.

By the way, just a little quibble. When we were discussing asynchronous vs. synchronous earlier, it didn't matter that you waited. That has NOTHING to do with sync vs. async. It was whether you had the OPTION to wait or not wait that made it one way or the other. What you choose to do with slack time doesn't matter. It is whether the computer OFFERS you slack time.

 

[gemma-the-husky]

I have NOT disrupted any normal flow. I send a query and wait for the result. I WAIT FOR THE RESULT!!! I

Surely, If your process waits for the results, you wouldn't be able to interact with the database until the query completes, and the code returns to process the next statement? Can you not add a sentinel of some sort to wait for the query to complete?

having read the last two posts

eg without changing the permissions settings simply do this

screen.backcolor = vbyellow
while screen.backcolor <> vbgreen
   doevents
wend

but now you need the asynchronous process to change the background colour, because this code will wait forever.

What ARE you actually able to do, before you get the result that is causing the issue?
Are you clicking a different form? Are you working outside access completely?

 

[pdanes]

Not a bad idea. And you are right... when the .Allow options block an action, a click that would attempt a blocked action simply gets ignored. Yet if you had another form open, its .Allow options could be different, so your blockage is selective to the offending form. Good luck on the rest of your project.

By the way, just a little quibble. When we were discussing asynchronous vs. synchronous earlier, it didn't matter that you waited. That has NOTHING to do with sync vs. async. It was whether you had the OPTION to wait or not wait that made it one way or the other. What you choose to do with slack time doesn't matter. It is whether the computer OFFERS you slack time.

I don't see that. EVERY process that communicates with another process has the option of waiting for a response or not. There is no such thing as not having the option of waiting. There is of course specific software that does or does not wait, and the user of that software is limited to what the software is able to do, but it can ALWAYS be written to either wait or not.

What makes processes synchronous or not is whether each side DOES wait for a response from the other.

 

[pdanes]

Surely, If your process waits for the results, you wouldn't be able to interact with the database until the query completes, and the code returns to process the next statement? Can you not add a sentinel of some sort to wait for the query to complete?

I thought so initially, but that is not what is happening, hence this thread. If you read the entire thread, you will see in the discussion that I have written, many times, that my code IS HALTED, waiting for a response from SQL Server to begin filling the requested recordset. It is during this wait period that the users occasionally click something, and manage to provoke Access into an unwanted response. When the error is dismissed, Access continues with the very next statement after the OpenRecordset call, since by that time, the server has responded.

having read the last two posts

eg without changing the permissions settings simply do this

screen.backcolor = vbyellow
while screen.backcolor <> vbgreen
   doevents
wend

but now you need the asynchronous process to change the background colour, because this code will wait forever.

It won't wait, it will cycle around forever. And how would the backcolor ever change? Access is single-threaded, so while this loop is running, nothing else will ever run, so the backcolor will never change.

I do not have any sort of asynchronou process running, nor do I think Access is even capable of starting a second internal process. I do not know everything there is to know about Access, but I have been working with it for decades and have never heard about any such ability.

What ARE you actually able to do, before you get the result that is causing the issue?
Are you clicking a different form? Are you working outside access completely?

Nothing. I am WAITING for the server's response. I do not get any result that is causing any issue. Random user clicks in the app BEFORE I get my results are what is causing the issue.

 

[gemma-the-husky]

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.

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

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.

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.

 

[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.