Error when clicking too fast (1 Viewer)

[pdanes]

I have a database that is slow to perform some functions. When the users click a button before the task is finished, an error message pops up, saying something like, "Setting of macro or function to property Before Update or Field Validation Rule prevents Microsoft Access from saving data in this field." (Translated - it's not an English system.}

The form is unbound, and there are no BeforeUpdate events, nor any validation rules. I cannot think where to trap the error, or even if it as trappable, since it occurs randomly pretty much everywhere if a control is clicked while it is still busy.
Is this normal? Is there a way to make it stop happening? I have some rather hyperactive users, and telling them to take it a bit slower usually works for about an hour, then they forget and go into warp drive again.

 

[The_Doc_Man]

To the good, it sounds like the error doesn't allow a change to happen because of some setting that prevents it. To the bad, you have to wonder if there are times when their anticipatory clicks actually DO something that you don't catch right away.

How much control do you have while one of those slow functions occurs? It this slow function perhaps something occurring purely via code behind some event? You might be able to put up a form that moves to the top of the form stack (on the z-axis) and says "BUSY - PLEASE WAIT" and if needed, have the form intercept mouse clicks until the code behind the scenes closes it again.

 

[CJ_London]

Is it only the one form? And any control? - you said a button do which is it.

doubt anyone can suggest anything without seeing the form design and underlying code

only suggestion I can make is if you have a main form/subform design, moving from one to the other might be a reason

 

[ebs17]

I have a database that is slow to perform some functions.

The database is innocent. The problem is mostly the applied programming.
A button click itself cannot take too long. So you should analyze the procedures that are initiated, and hit the developer.

 

[Pat Hartman]

Having unbound update forms is what is abnormal. When you are running a long process, you can display a popup form with a "busy" indicator. Then when control returns to the procedure,close the popup.

 

[pdanes]

It's not one form, it's pretty much all the forms, and it's kind of random. Sometimes it happens, sometimes it doesn't. I can make it happen pretty reliably by clicking on a button that starts a lengthy task, like getting something complicated from the linked SQL Server database. If I click another button before this completes, I get the error.

Unbound forms do this, despite having no link to anything that needs to be updated. I see nothing abnormal about an unbound form. They are a normal construct, used all the time in all sorts of situations.

I can't always know what will take a long time, Some tasks are quick sometimes, slow others. Some tasks are dependent on a number of settings, and will be slow with some settings and fast with others. There are dozens of controls on some forms, and putting code into every one to disable all input, or pop up a blocking dialog would be a monumental PIA.

I would have thought that Access could queue clicks and deal with them in the order received, but it doesn't seem to be working that way.

 

[Pat Hartman]

They are a normal construct, used all the time in all sorts of situations.

Access is a RAD tool. The whole point of a RAD tool is to make use of its capabilities. Otherwise, why would you saddle yourself with an environment with such a heavy footprint and lack of flexibility? The best thing about Access is bound forms and you've thumbed your nose at them.

I would have thought that Access could queue clicks and deal with them in the order received, but it doesn't seem to be working that way.

It does but you have disconnected the BE updates from the FE so Access cannot control what it doesn't know about. YOU should know when you are writing the update procedures what the potential impact is. If you are updating one record, NP. If you are potentially updating thousands, then popup up a form with "waiting". Then you have to figure out how to tell when the disconnected update is complete. You'll also need to run a timer event to loop through the "finished" checks so you know when to close the popup. If you are using stored procedures, you can have them post a result in a table that you can check. Once you introduce timers into your app, you need to protect yourself from them so give yourself a way to turn them all off when you are making object change, unless of course you are testing the timers, or you will be sorry.

 

[pdanes]

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.

Saying I 'have to know' how long a query will take is silly. I have no way of knowing in advance what will be impacted, or what the load is on the server at any particular instant. Much of what the main form does is lookups, and until I do the lookup, I have no possible way of knowing how long it will take. Some queries are very simple, but still take a long time. Others may be very complex, but return results instantly. There is no way to know for certain just from the construction of the query - there are many possible filtering parameters, and joins across varying numbers of tables.

And again, putting local disabling code FOR every control, INTO every control would be insanely tedious. It would run slowly, cause all sorts of screen flickering and probably be so prone to errors that it would cause more problems than it solves. If this behavior is just the way Access works, and it can't handle queued clicks, then I will just have to tell the users to live with it, but it seems a sorry state of affairs from an otherwise decent bit of software.

 

[ebs17]

I would have thought that Access could queue clicks and deal with them in the order received

VBA works synchronously, one step at a time. If you start asynchronous processes, you could encapsulate them and provide them with an exit code, with which subsequent routines then check this exit code before starting.

In addition, I would not take it for granted and that it cannot be changed that procedures are slow, i.e. they take too long. A written statement does not have to be a good or an optimal statement. The ability to criticize, also towards oneself, should not be a foreign word.

 

[GPGeorge]

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.

Saying I 'have to know' how long a query will take is silly. I have no way of knowing in advance what will be impacted, or what the load is on the server at any particular instant. Much of what the main form does is lookups, and until I do the lookup, I have no possible way of knowing how long it will take. Some queries are very simple, but still take a long time. Others may be very complex, but return results instantly. There is no way to know for certain just from the construction of the query - there are many possible filtering parameters, and joins across varying numbers of tables.

And again, putting local disabling code FOR every control, INTO every control would be insanely tedious. It would run slowly, cause all sorts of screen flickering and probably be so prone to errors that it would cause more problems than it solves. If this behavior is just the way Access works, and it can't handle queued clicks, then I will just have to tell the users to live with it, but it seems a sorry state of affairs from an otherwise decent bit of software.

One possible approach is to assume that you will anticipate the problem sometimes occurs and that you, the developer who adopted unbound forms, are now responsible for coding every aspect of the process, up to and including putting in steps to handle slow processes if and when they should occur.

You could, perhaps, take your coding to that next level and introduce a module that DOES intercept and queue user responses inasmuch as that's where the problem lies. It's one you introduced inadvertently by adopting unbound forms, but now that you've done so, it's all on you to handle it.

 

[pdanes]

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

I assemble a text string, send it off to the SQL Server engine, and wait for a response. There is a timeout parameter in pass-through queries exactly for queries that take a long time, and I am nowhere near hitting that. It is generally a matter of seconds until I get a response, but that is more than enough time for a user to start clicking buttons. My code is waiting for a response, and ALL activity should halt until it gets that response, or gets a timeout error. Some of you have told me that this is what Access does, while at the same time telling me that I have to handle asynchronous activity.

So, which is it? Why does Access not queue clicks while this code waits for a response? Is waiting for a response from a server any different from say puutting together a huge text string, or running a long looping procedure? If so, how? If not, why does Access throw this nonsense error when waiting for one, but not the other?

 

[Pat Hartman]

If this behavior is just the way Access works, and it can't handle queued clicks, then I will just have to tell the users to live with it, but it seems a sorry state of affairs from an otherwise decent bit of software.

As I said. You have disrupted the normal flow of the RAD tool. Don't blame it on Access.

I'm also not sure why your lookups are so slow, maybe you should try to optimize them. How many records can a user effectively "process"? The whole point of using a RDBMS BE is to let the server do the heavy lifting so why would you be returning hundreds of thousands of rows? I have apps that use bound forms that deal with tables containing millions of rows. My apps aren't slow at all. Maybe because I never bring back thousands of rows that the user can't possibly "process". In my search forms, I use a count query before I retrieve any data. If the count is 1, I open the edit form. If the count is > x, I ask them to add more criteria although I do give them the ability to just bring back what they selected after I tell them it will be slow. Otherwise, I open a form that shows a list of the important data fields which they can filter further and from there pick the record they want to edit and open the edit form.

In the cases where I have batch processing to do, it isn't handled by users pressing a button and waiting for a response. I create a macro that runs the batch process and then closes the app. The code in the procedure the macro runs also logs errors and completion and run time. I use the Windows scheduler to schedule these jobs in the overnight window. Each batch process is run by a .bat file that opens the app and passes in the name of the macro that kicks off the process. Two users (you don't want to forget to do this) are scheduled to look at the results first thing in the morning so if there was a problem someone can address it.

 

[pdanes]

VBA works synchronously, one step at a time. If you start asynchronous processes, you could encapsulate them and provide them with an exit code, with which subsequent routines then check this exit code before starting.

In addition, I would not take it for granted and that it cannot be changed that procedures are slow, i.e. they take too long. A written statement does not have to be a good or an optimal statement. The ability to criticize, also towards oneself, should not be a foreign word.

But I'm NOT starting an asynchronous process. I'm submitting a query and waiting for the response. I think Access should queue up whatever happens while I'm waiting.

 

[GPGeorge]

But I'm NOT starting an asynchronous process. I'm submitting a query and waiting for the response. I think Access should queue up whatever happens while I'm waiting.

You assumed responsibility for the queue because you adopted the unbound approach. However you handle it, you must go the next step and set up an approach -- such as the one ebs17 suggested -- that does account for the lack of processing lost when bound forms were not used.

 

[ebs17]

I'm submitting a query

How exactly? When executing a very long-lasting query, Access seems frozen and would not react to new procedure calls, let alone produce subsequent error messages.

If you filter: what happens to the result? Shouldn't that be displayed somehow? What is the purpose of another button click out of impatience?

 

[Pat Hartman]

So, which is it? Why does Access not queue clicks while this code waits for a response? Is waiting for a response from a server any different from say puutting together a huge text string, or running a long looping procedure?

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.

If you start asynchronous processes, you could encapsulate them and provide them with an exit code, with which subsequent routines then check this exit code before starting.

That is what I suggested earlier.

But I'm NOT starting an asynchronous process. I'm submitting a query and waiting for the response. I think Access should queue up whatever happens while I'm waiting.

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?

 

[Petr Danes]

I have NOT disrupted any normal flow. I send a query and wait for the result. I WAIT FOR THE RESULT!!! I am not spawning any process that runs off on its own. If I was, I wouldn't be having this problem, because Access would not be waiting for anything, and could handle the clicks. The whole problem is that it does not handle the clicks, because it is waiting. The click interrupts something and makes it throw an error, instead of properly waiting to be handled in turn.

I don't know where you got your 'hundreds of thousands' notion. Certainly not from anything I wrote. There are about 130,000 records in the entire database, and I do not return anywhere near all of them. But there are complex bindings in the database, and convoluted filtering conditions in some of the queries - even counts can sometimes take a few seconds.

I do not do any batch processing. That is a very antiquated concept, applicable to large runs handling large volumes of data. This app does not do anything like that. It handles real-time user interactions, selecting a few records, or at most a few dozen in extreme cases, updating generally one record in one table, or sometimes one record in each of a few linked tables. But response is not instant - it can take a few moments, but that is long enough for a user not paying attention to start another task.

As I said. You have disrupted the normal flow of the RAD tool. Don't blame it on Access.

I'm also not sure why your lookups are so slow, maybe you should try to optimize them. How many records can a user effectively "process"? The whole point of using a RDBMS BE is to let the server do the heavy lifting so why would you be returning hundreds of thousands of rows? I have apps that use bound forms that deal with tables containing millions of rows. My apps aren't slow at all. Maybe because I never bring back thousands of rows that the user can't possibly "process". In my search forms, I use a count query before I retrieve any data. If the count is 1, I open the edit form. If the count is > x, I ask them to add more criteria although I do give them the ability to just bring back what they selected after I tell them it will be slow. Otherwise, I open a form that shows a list of the important data fields which they can filter further and from there pick the record they want to edit and open the edit form.

In the cases where I have batch processing to do, it isn't handled by users pressing a button and waiting for a response. I create a macro that runs the batch process and then closes the app. The code in the procedure the macro runs also logs errors and completion and run time. I use the Windows scheduler to schedule these jobs in the overnight window. Each batch process is run by a .bat file that opens the app and passes in the name of the macro that kicks off the process. Two users (you don't want to forget to do this) are scheduled to look at the results first thing in the morning so if there was a problem someone can address it.

 

[Petr Danes]

How exactly? When executing a very long-lasting query, Access seems frozen and would not react to new procedure calls, let alone produce subsequent error messages.

So I would think. But it does, which is the entire reason for this post.

I assemble a text string, like "Exec FiltrOrigin.spAkcesity @Poznamka = 'ju'", put it into a pass-through query, and use a normal CurrentDB.OpenRecordset to get the results back. The OpenRecordset command should wait until that recordset materializes, or until the query times out (which does not happen).

 

[Petr Danes]

What part of asynchronous do you not understand?

I understand asynchronous just fine. What I don't understand is why you think I'm spawning an asynchronous process. I'm not. I'm submitting a query and waiting for the results to come back. That is the exact opposite of an asynchronous process.

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.

It doesn't need to know what's going on. It only needs to wait for a response. I'm beginning to think YOU don't know what an asynchronous process is.

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

I did NOT!!! I don't see why you can't get this through your head. I am submitting a query and waiting for the results.

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?

No - there are other graphic things that happen AFTER the query completes and results come back. None of those are happening, exactly as they should not be. The only thing happening is that Access is throwing this dumb error message WHILE WAITING for the query results.

 

[Petr Danes]

Another point, don't know if it's pertinent. The error message does not have the normal debug option. All it has below the error message is an Ok button. When that is clicked, the code continues, and everything works normally, because by that time the query has come back with its results. All I want is for Access to NOT respond to these clicks while its waiting for the recordset to open.