Error when clicking too fast

[Petr Danes]

This is essentially asynchronous. You trigger the query but then have to wait for it.

Well - yes. That is what makes it NOT asynchronous - I'm waiting. An asynchronous process would be one that I send off and then continue on to do something else. I don't, and in fact, I don't even know how I would do that. I'm sure I could find out with a bit of digging (a property in the pass-through query?), but I've never had occasion to do something like that. All my interactions with the engine are done this way. The fact that I COULD make an asynchronous process is another matter.

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.

I understand that. Again, I COULD make this asynchronous by not waiting, but I'm not - I AM waiting.

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

I really don't know what else to say about this. Yes, I could go on. I don't. I wait until I get back the records I requested. I know that the code is waiting because I single-step it during trouble-shooting, and I have graphic events that take place immediately after the call. Single stepping does NOT continue after submitting the query - it waits until the query completes before I even have the option of executing the next line of code. And when I run it normally, none of the graphic things happen until after the records are delivered.

Asynchronous means the two process are running on their own, with no regard for each other. Synchronous processes communicate and take each other into account. THAT is what I am doing. SQL Server sits and waits. My Access app submits a query. SQL Server accepts that query and gets to work on it. Access sits and waits until SQL Server notifies it that it now has the requested records, here they are. Access takes those records and resumes what it was doing. SQL Server goes back into standby mode. That is exactly a synchronous process - two tasks communicating with each other, and each waiting until the other finishes and announces that it is finished before resuming its own work.

Although, if I'm going to split hairs, there is a slight bit of asynchronicity. Access gets back to work as soon as it has at least one record, whether from SQL Server or it's own internal JET engine. In the background, the engine continues delivering records until all have been returned, but Access does not wait for the entire recordset before resuming processing.

 

[The_Doc_Man]

Although, if I'm going to split hairs, there is a slight bit of asynchronicity. Access gets back to work as soon as it has at least one record, whether from SQL Server or it's own internal JET engine.

This is not entirely a correct viewpoint. With either JET or ACE, the interface used between the GUI and the DB engine involves synchronous I/O through a formal channel. In order for you to see ANYTHING from an Access-to-ACE/JET connection, the query has to already be done so that your BE can present you with the first element of your result set/recordset. You then synchronously ask for subsequent results (if there are any).

With a pass-thru query to an SQL server or equivalent "active SQL engine" back-end, that query was not synchronously connected, but rather involved an async connection. You have to decide whether the action is complete before you can retrieve data.

To emphasize the difference, if you did that same readiness test with JET/ACE, you would ALWAYS find the action to be complete. With an active SQL back-end, you MIGHT find the action to be complete, or might not.

To the best of my knowledge, with JET/ACE, the ONLY asynchronous activity would be a cleanup phase inside the back-end DB that does not present anything to any connections. The query itself is synchronous.

 

[Petr Danes]

This is not entirely a correct viewpoint. With either JET or ACE, the interface used between the GUI and the DB engine involves synchronous I/O through a formal channel. In order for you to see ANYTHING from an Access-to-ACE/JET connection, the query has to already be done so that your BE can present you with the first element of your result set/recordset. You then synchronously ask for subsequent results (if there are any).

With a pass-thru query to an SQL server or equivalent "active SQL engine" back-end, that query was not synchronously connected, but rather involved an async connection. You have to decide whether the action is complete before you can retrieve data.

To emphasize the difference, if you did that same readiness test with JET/ACE, you would ALWAYS find the action to be complete. With an active SQL back-end, you MIGHT find the action to be complete, or might not.

To the best of my knowledge, with JET/ACE, the ONLY asynchronous activity would be a cleanup phase inside the back-end DB that does not present anything to any connections. The query itself is synchronous.

Not in my experience. Even with local Jet, if I open a large recordset, checking the record count immediately after the OpenRecordset command, the count is less than the total. Repeatedly checking the count shows that it gradually increases. The only way to make sure you have all the records is to issue the .MoveLast command, and with a lare recordset, that can produce a noticeable delay.

My copy of Getz, Litwin and Gilbert is at work, but here is one site explaining that, and I'm sure there are many others.

https://learn.microsoft.com/en-us/p...007/bb243791(v=office.12)?redirectedfrom=MSDN

And I still don't understand why you're using the word async to describe the pass-through query. Access submits a query and waits until the DB engine responds. It doesn't matter whether the source is SQL Server or Jet. It issues a request and waits for a response. That is a pair of synchronous processes communicating over a task.

 

[Dave E]

What part of asynchronous do you not understand? When you send a request to the server this way, it is totally disconnected from the Access GUI so the GUI doesn't know what is going on. In a code loop, either one of yours or one created by the form's class module as it runs your form, the world is locked out until the code finishes. That is why if you want to keep the user updated as to where you are, you need to use Do Events to release control temporarily to Windows in order to allow you to refresh the form so you can update the record count or whatever you are using to inform the user of progress.

That is what I suggested earlier.

From your mouth to God's ear. That isn't the way Access works. The problem is by creating an asynchronous process, you told Access to NOT wait for a response but to continue

Test it yourself. For one of the queries that takes a while, add a msgbox on the line after. Does the message box pop up BEFORE the query ends or after?

You've lost the patient approach you used to have, Pat; not the same Pat from 20 years ago.
I have a personal rule that says I won't be rude unless someone is rude to me. It might help you.

 

[Petr Danes]

You've lost the patient approach you used to have, Pat; not the same Pat from 20 years ago.
I have a personal rule that says I won't be rude unless someone is rude to me. It might help you.

AND he's wrong. As I've written here repeatedly, the code after the query call does NOT execute until AFTER the server responds. Access waits for the DB engine's response.

 

[The_Doc_Man]

Not in my experience. Even with local Jet, if I open a large recordset, checking the record count immediately after the OpenRecordset command, the count is less than the total. Repeatedly checking the count shows that it gradually increases.

You are confusing two events. The JET/ACE engine completes the query - but then AS A SEPARATE ACTION it starts loading the recordset. Your action to check the recordset is different than completion of the query.

And I still don't understand why you're using the word async to describe the pass-through query. Access submits a query and waits until the DB engine responds. It doesn't matter whether the source is SQL Server or Jet. It issues a request and waits for a response.

If you are talking about SQL Server and a pass-thru query, the response is that SQL Server accepted and started running the query.

If you are talking about JET/ACE and a query, pass-thru or otherwise, the response is that the DB engine completed the query.

 

[Dave E]

Forget the current OP, Pat. This is becoming a trend for you. You're showing a genuine lack of patience and the wording of your replies are sometimes demeaning to users looking for answers, not incivility.

It's not that you need to seek forgiveness from anyone but you should count to 10 before you reply and consider your responses more carefully.

 

[Petr Danes]

You are confusing two events. The JET/ACE engine completes the query - but then AS A SEPARATE ACTION it starts loading the recordset. Your action to check the recordset is different than completion of the query.



If you are talking about SQL Server and a pass-thru query, the response is that SQL Server accepted and started running the query.

If you are talking about JET/ACE and a query, pass-thru or otherwise, the response is that the DB engine completed the query.

NO, it's not. I've tested this many times. Both SQL Server and Jet respond with the first record requested record and not before. SQL Server does NOT respond with a message saying, "Okay, I'll get to work on that." It responds with the recordset. Sometimes with just the beginning and gradually fills in the remainder, sometimes with all if it is a small set. Jet does exactly the same. And in both cases, Access waits for that response.

I don't know what to say when you tell me that something is happening which I can clearly see with my own eyes is not happening. I issue an OpenRecordset command, with a pass-through or a local, and Access STOPS AND WAITS until records start coming back. It does NOT continue on it's way UNTIL records start coming back.

If it does that for you, then you are doing something vastly different from me, and I don't even know what that might be. In any case, I see no point in continuing this. I appreciate the tips, and I have something to experiment with when I get back to work next week, but I think we're no longer making any headway.

 

[Petr Danes]

We are over 50 posts into this thread and the OP is still arguing with us and hasn't yet tried the suggestion I made very early on so please forgive me for getting a little testy.

I'm not at work now - shop is closed for the holiday. I can't try anything more until I get back to work, but I have thanked those who provided a testable suggestion, and have made notes of what I intend to try. And I'm only disputing things I know from my own experience - specifically the question of whether Access continues without waiting for a response after issuing an OpenRecordset command. It does not.

 

[The_Doc_Man]

NO, it's not. I've tested this many times. Both SQL Server and Jet respond with the first record requested record and not before.

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

 

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

 

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