Users Quit Program During Long Queries (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 09:02
Joined
Dec 20, 2007
Messages
2,061
Is there anyway to keep user from closing the Database during long Queries?

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:02
Joined
Oct 29, 2018
Messages
21,357
How exactly are users "closing" the database? There are ways to prevent it, disabling the close button, for example, but other ways are unpreventable, e.g. turning off the computer or killing the Access process.
 

Dreamweaver

Well-known member
Local time
Today, 13:02
Joined
Nov 28, 2005
Messages
2,466
Why are your queries taking so long that the questions I would want to answer
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
The best way is if you have a switchboard form or some other method to prevent the users from seeing the underlying structure of the DB, you put an OnUnload event on that form. The Unload event has a Cancel option and if you cancel the Unload, the Form_Deactivate and Form_Close events will not occur. No Form_Close, no DB close.

https://docs.microsoft.com/en-us/office/vba/api/access.form.unload

There is a little code snippet in the example that asks for confirmation of closing the form before allowing it to proceed. If you apply this, I guess you would have to have something internal, some flag or another in a public area in a general module OR in a TempVars variable, that knows you started a long-running query. While that flag is set (TRUE) you disallow the form to close. OR it might be that you lock the form from which the query was triggered even if not a switchboard or dispatcher form.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 09:02
Joined
Apr 27, 2015
Messages
6,280
Thales750

I had a similar issue where there was just no speeding up the process. The answer is to give the user something to look at while the query is running in the background.

I have a bit of code that runs a spinner in an IE instance and the quits when the query has finished. If you are interested, I can share it.
 

Thales750

Formerly Jsanders
Local time
Today, 09:02
Joined
Dec 20, 2007
Messages
2,061
During the migration to SQL Server we decided to run some test.

There was a loop that was replaceable with a a few queries. That operation was the bottleneck. It hid in the network traffic. when a PC was running slow, or the network was busy, the problem got worse, of course.

Now the whole process takes about 5 seconds.
 

isladogs

MVP / VIP
Local time
Today, 13:02
Joined
Jan 14, 2017
Messages
18,186
Much better but for some users even 5 seconds is too long to wait :rolleyes:
In any lengthy query, I add a visual aid so the user knows something is happening
e.g. A prominent message on screen such as "Running query. This will take a few seconds..." or a progress bar or spinner etc.
Of course either of the latter two will slightly increase the time taken...but it does help keep users happy
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
Like Colin, I had problems with a few long-winded processes. For the ones that took a few seconds, I changed the color and caption of the command button from a basically pale aqua box around darker blue text to a pale yellow box around dark red text, with the word "Commit" becoming "Running..." When it finished, the box would vanish until the form got dirty again. Not saying any one way is better than any other, but the point is that you CAN advise users that stuff is happening.

As part of the process, I sometimes had to build external spreadsheets or import them. What I did for those processes where I had stepwise control (as opposed to a monolithic query) was I built two overlapping rectangles. In my case, long and skinny, with thin borders. Normally they were invisible. However, if one of these long-winded processes would start, I would make them visible and diddle with the length of the one that was "on top." I would set its width to 1 twip, change its background to a contrasting color, and then at each step, I would recompute its width as a percent of the width of the bottom box (which was constant in length). When the ugly process was done, both rectangles would vanish.

This post isn't meant to say this is the only way but rather is just to give you ideas of how to show your users that something IS happening.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
Yep, just a different kind of home-grown progress bar. I didn't use percentages, just solid bars, and I used a formula that was based on knowing exactly how many steps I would need (usually because I had a count of elements.) Like I said in the post, just another way of doing things. However, mine was continuously variable to a fairly high precision.

I used a formula like this, X is the current value and XMax is the known maximum value:

recTop.Width = X * recBot.Width / XMax

All elements were LONG. If I had recBot set to be 5 inches on screen, that would be something over 7200 distinct steps. I rarely needed THAT many, but sometimes when an archive step was under way, I might have a thousand records.

Here's the drawback. If you have TOO many steps - like in the tens of thousands - then the time taken to redraw the bar became an issue. Therefore I sometimes set it up to only do every 5 steps or so. In that case, knowing the percentage had changed enough took less time since I didn't do the .Repaint every time.
 

deletedT

Guest
Local time
Today, 13:02
Joined
Feb 2, 2019
Messages
1,218
Doc
What you are describing is how a progress bar works...or at least its how my progress bars work.:)
See http://www.mendipdatasystems.co.uk/progress-bar/4594424316 for code and screenshots

I've never seen a progress bar that works. Sometimes it's about 60% and all of a sudden jumps to the end, and it's finished.

I'll try your sample code tomorrow morning as soon as I'm back to work.
Your type of codes tell me "At last I found a progress bar that works"
...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
Every now and then I would see that behavior but I think that was because of some competing process unrelated to Windows. The graphics processing would sometimes get skipped until the process ended. But part of that was because I had a hidden form timer that was repeatedly checking for a status change that would have asked everyone to please log out (i.e. for scheduled or emergency maintenance.)
 

isladogs

MVP / VIP
Local time
Today, 13:02
Joined
Jan 14, 2017
Messages
18,186
Doc
I do exactly the same. Identify the number of steps and increase the width of the progress bar proportionately after each step together with updating the % complete value. The effect is smoother when there are a relatively large number of steps but any value will work
Similarly when a huge number of steps are used, I sometimes limit the updating to every 10th step or whatever.
Of course, like all progress bars, if an individual step takes a long time, the progress indicator can appear to be stuck for a while

Tera
This approach definitely works and, provided you count the number of steps correctly, avoids the issue you described..
For simplicity, the demo uses a timer event to make the bar progress but the effect is the same

I thought it was also available somewhere on this forum but the advanced search has let me down ....as I can't find it!
 

deletedT

Guest
Local time
Today, 13:02
Joined
Feb 2, 2019
Messages
1,218
Tera
For simplicity, the demo uses a timer event to make the bar progress but the effect is the same

@isladogs, what do you do normally? Do you update the progress bar after executing each step (instead of using timer)?

thanks
 

isladogs

MVP / VIP
Local time
Today, 13:02
Joined
Jan 14, 2017
Messages
18,186
Hi Tera
Yes. The progress bar is normally updated after each step.
 

isladogs

MVP / VIP
Local time
Today, 13:02
Joined
Jan 14, 2017
Messages
18,186
Would you upload a sample so I can compare using the code in a class module rather than a standard module
 

deletedT

Guest
Local time
Today, 13:02
Joined
Feb 2, 2019
Messages
1,218
Check the attached file.

My application is too complicated and I couldn't upload mine. I changed your own file in a hurry. So some parts may be different with the actual code I used.

The core is kept as it was.
 

Attachments

  • ExampleProgBarWithImage.accdb
    432 KB · Views: 31
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:02
Joined
Jan 14, 2017
Messages
18,186
Thanks. Had a quick look and it still works correctly.

What advantage do you see in doing this as a class module?
Both methods are transferable for use in multiple forms within an application but the class approach requires additional code.
 

Users who are viewing this thread

Top Bottom