Fast 5th Generation SSD Transfer Rate

[Albert D. Kallal]

I put my long running DLOOKUP's inside DoEvents, and the UI still displays "Not Responding"

Right, as I explained, calling ACE/JET code means the code waits - just like if you called a VBA sub routine. It's blocking, and Access will have to simply wait until that dlookup is done - it's not ANY different then you having to wait for a VBA routine to complete. DoEvents() will thus not help.

However, it is a "common" Access myth that dlookups() are slow - they are not any slower then say using VBA code and a recordset to do the same thing.
However, it not that dlookup() is slow, it tends to be WHEN and WHERE it is used that is slow!
So for example, you NEVER want to use a dlookup() inside of Access SQL query - there are alternatives.

However, if you talking about binding dlookup() to controls on a form? Well, in fact you do get some threading here. If you have multiple dlookups() on that form? Then in-between each dlookup() Access can using it's threaded model update the screen, even if all dlookups() are not yet done. However, for EACH dlookup(), Access MUST and WILL wait, and during that wait time, Access can't do anything else except wait....

And often in place of 3 or 4 dlookups() on that form? Well, often you want several columns from the same data row. In that case, I suggest writing a custom routine, and use that to get the data, since with one data "row" pull, you can then use/get multiple columns from that row. With a mutliple dlookups(), you are re executing a full SQL query against the database for each dlookup() call. And if those dlookups() are resolving to the same (one) database row? Then in place of 5 dlookups(), you can build a function, and do only ONE database pull.

So, a pull of data from the database (dlookup(), VBA recordSet, or even a SQL query)? They all are blocking, and doevents() can't help. Access will wait, and the UI will be frozen during that time. If you adopted SQL server, then you could do many of these operations without blocking (freezing the UI).

However, if you pull into a reocrdset, and use a "loop" to process the data, and do a sum of the data using that loop? Then a doEvents say every 1000 records in fact would allow the UI to update before that processing is complete....

 

[BlueSpruce]

if you talking about binding dlookup() to controls on a form? Well, in fact you do get some threading here. If you have multiple dlookups() on that form? Then in-between each dlookup() Access can using it's threaded model update the screen, even if all dlookups() are not yet done. However, for EACH dlookup(), Access MUST and WILL wait, and during that wait time, Access can't do anything else except wait....

I am firing the DLOOKUP's in the On Open event of an unbound form. The form doesn't display any results until all 42 DLOOKUp's complete. In the meantime, I get "Not Responding" and the popup modal grays out.

 

[Albert D. Kallal]

I am firing the DLOOKUP's in the On Open event of an unbound form. The form doesn't display any results until all 42 DLOOKUp's complete. In the meantime, I get "Not Responding" and the popup modal grays out.

View attachment 122276

Ah, very well then!

Ok, so do you have code in which a whole bunch of dlookups() are triggred by VBA then?

If yes, then I suggest several things:

First up, move such code to the on load event. On open is too soon, and the form display (and the display thread) has NOT YET been started by Access.

Keep in mind that on open event can view bound data values (you don't have any), but it can't modify them.....

And the open event of a form has a cancel parameter - if you set = true, then the form will never display.
(and, doEvents will NEVER help to display things!!!)


So, for "general" setup of a form? That code needs to be placed in the on load event. Save that event for ONLY to verify things, and PREVENT the form from ever loading and that form can't and will not display anything until the on open event is 100% done.

So, in fact, if you have a bunch of VBA dlookups()? Move that code to the on-load event.
In fact, you might not even need to place do events.

So, first, try the on-load event - it's better, since the form will have at least displayed.

Next, if each dlookup() does not display, then of course place a doevents between each one.

eg:
txtLoans = dlookup(bla bla bal)
doevents
txtInterest = dlookup(bla bal bal).
doevents
....more dlookups() + do events follow

So, for sure move this code to on load event. And if it does not start displaying each result, then try the above DoEvents between each dlookup()

So MUCH of the issue here is having used the on open event - as I pointed out, don't use that event for form "setup" code, and as I noted you can't even modify bound controls - they are read only, and updating not even allowed. And as noted, not only can't you update bound controls the display "thread" will not yet have started - doEvents can't and will not help in that on-load event.

So,
on open event - testing if form has valid data - set cancel = true, form will NEVER display.

on load event - general form setup code goes here...

R
Albert

 

[cheekybuddha]

I am firing the DLOOKUP's in the On Open event of an unbound form. The form doesn't display any results until all 42 DLOOKUp's complete. In the meantime, I get "Not Responding" and the popup modal grays out.

View attachment 122276

Frank, didn't you go through all this already a few years ago back on UA?

Why fire 42 queries when you are fetching identical data in six time periods?

You can do the same with a maximum of 7 queries and probably fewer.

If you want help sorting out speeding this up you should provide the tables, queries, fields, indices and criteria, and info about whether BE is Access or RDBMS.

 

[BlueSpruce]

Frank, didn't you go through all this already a few years ago back on UA?

You keep thinking I am Frank, and I'm not. I am Efrain Pino, who use to work with him and bought his business when he bought a fleet of trucks and no longer does computer work. I am very familiar with his works.

Why fire 42 queries when you are fetching identical data in six time periods?

I agree that his code can be optimised. Nevertheless, the issue here is how to prevent the Windows "Not Responding" when running the current code, or any other code that takes a long time to complete. Has anyone else's Access app experienced that situation?

 

[The_Doc_Man]

the issue here is how to prevent the Windows "Not Responding" when running the current code, or any other code that takes a long time to complete. Has anyone else's Access app experienced that situation?

In my genealogy DB, it happened all of the time. Still happens now and then. I'm parsing my way through data for 2250 people (roughly) and the "Not Responding" kicks in. What I did was added a progress bar that updated every so often and made it do a screen refresh. Apparently that break in the action helped it know that Access was legitimately busy and therefore it complained less often. I had to add code so that it would do that only every 100 persons or so. Doing so was a balancing act. Too many updates became expensive in time, badly slowing down the desired process by wasting time on video cosmetics; too few updates let the "not responding" blurb pup up anyway.

 

[BlueSpruce]

In my genealogy DB, it happened all of the time. Still happens now and then. I'm parsing my way through data for 2250 people (roughly) and the "Not Responding" kicks in. What I did was added a progress bar that updated every so often and made it do a screen refresh. Apparently that break in the action helped it know that Access was legitimately busy and therefore it complained less often. I had to add code so that it would do that only every 100 persons or so. Doing so was a balancing act. Too many updates became expensive in time, badly slowing down the desired process by wasting time on video cosmetics; too few updates let the "not responding" blurb pup up anyway.

So after I refactor the DLOOKUP code and move it to the On Load event, as @Albert D. Kallal suggested, I should repaint the form after each query. At least the users will see the results little by little without getting that dreaded "Not Responding" message. The users were calling me saying the program had a bug when they read that message and saw the screen grey out. Wonderful choice of words MS. Why not just say "BUSY"?

How does Windows know if a program is not responding?

 

[Albert D. Kallal]

yes, give my suggesting a try - it should then update each dlookup() as they complete.


Funny how one "little" tid bit of information (the on-open event) gives rise to a solution here.
I'm actually quite confident this will work for you....

You not need a repaint - the doEvents should suffice here. (it will allow the paint event to complete anyway).

Do post back and share how this worked!
R
Albert

 

[BlueSpruce]

yes, give my suggesting a try - it should then update each dlookup() as they complete.


Funny how one "little" tid bit of information (the on-open event) gives rise to a solution here.
I'm actually quite confident this will work for you....

You not need a repaint - the doEvents should suffice here. (it will allow the paint event to complete anyway).

Do post back and share how this worked!
R
Albert

I must first refactor these awful DLOOKUP's and queries before moving them to the On Load event. There's 21 pairs of the following DLOOKUP's with their associated queries. I'm thinking I can just use 6 queries, one for each time period, instead of 42 DLOOKUP's and do away with the Immediate If's. What a mess!

Me.AllPrincipalCount = IIf(IsNull(DLookup("TotCount", "qryTotalsSystem", "TransType = 'Principal'")), 0, DLookup("TotCount", "qryTotalsSystem", "TransType = 'Principal'"))
Me.AllPrincipalAmount = IIf(IsNull(DLookup("TotAmount", "qryTotalsSystem", "TransType = 'Principal'")), 0, DLookup("TotAmount", "qryTotalsSystem", "TransType = 'Principal'"))


qryTotalSystem:

SELECT tlkpContractTransType.TransactionGroupType AS TransType, Count(tblContractTransactions.ContractTransSIID) AS TotCount, Sum(tblContractTransactions.TransactionAmount) AS TotAmount
FROM tblContract INNER JOIN (tblContractTransactions INNER JOIN tlkpContractTransType ON tblContractTransactions.ContractTransTypeSIID = tlkpContractTransType.ContractTransTypeSIID) ON tblContract.ContractNo = tblContractTransactions.ContractNo
WHERE (((tblContract.ContractStatusSIID)>1))
GROUP BY tlkpContractTransType.TransactionGroupType;

 

[Albert D. Kallal]

Fair enough!! - and I VERY much like that you admit that what you have is less then ideal.....

However, that's life! - You certainly can "improve things" - always a great idea!

Regardless of the above, placing a doEvents between each dlookup will fix the freezing, and the controls will thus "update" as the dlookup(s) complete. The "key" or "Rosetta" stone here?

Moving this to on-load - the code does not have to change - and it will most likey fixed the "not responding" isssue(s).

However, as you note, you also want to give that code and "boatload" of dlookups() some love and care!

Do share how this turns out - I'm sure it's going to work out rather well!!!

R
Albert

 

[BlueSpruce]

Fair enough!! - and I VERY much like that you admit that what you have is less then ideal.....

However, that's life! - You certainly can "improve things" - always a great idea!

Regardless of the above, placing a doEvents between each dlookup will fix the freezing, and the controls will thus "update" as the dlookup(s) complete. The "key" or "Rosetta" stone here?

Moving this to on-load - the code does not have to change - and it will most likey fixed the "not responding" isssue(s).

However, as you note, you also want to give that code and "boatload" of dlookups() some love and care!

Do share how this turns out - I'm sure it's going to work out rather well!!!

R
Albert

The first obvious improvements are to use Nz(DLookup(...) instead of IIf(IsNull(DLookup(...)) , refactor the queries, and use bound controls.

I inherited 63 projects and must continue curating them for the customers who are using them. They seem to be working because I have not received any frantic calls from customers. So I am thinking "If it's not broken, don't fix it", but on the other hand I can improve things like the "Not Responding" so customers don't see those AppHang popup windows. Since there's no guarantees they won't show up, even if I improve the Access apps, is there any way to supress those popup's?

 

[Albert D. Kallal]

is there any way to suppress those popup's?

Not really, but on the other hand? If you remove "poor" design decisions made, then 9 out of 10 times, you not experience any "not responding" messages.
In this case, the developer gave no thought as to why and when to use "on-open" vs "on-load". A basic mistake, and for me, I would have instant cringed that on-open was being used here! It's not the right event.

I mean, when you buy a car, there no information in the owner's manual that you should drive your car backwards down the freeway in reverse gear! That's going to cause problems - no matter how good (or bad) the car is!!

Had the developer realized that on-open has a "purpose" and setup of controls and code does NOT belong in that event?
Then the freeze up and the "not responding" probably would not have occurred here.

So, ultimately to prevent and not have those "not responding" issues?
Why of course simply adopt sound designs.....

In this case? The wrong approach was used - on-open is simply the wrong event, and EVEN adding doEvents will not fix this issue.

A simple cut + paste and moving that code into the on-load event will fix this example. Likely some doEvents may well be required, but regardless, the correct design choice(s) in the first place would have prevented this freezeup....

It really don't matter what dev tools and stack you use - bad decisions are, well, bad decisions.

Just last week, I had a page (one that I designed - so, I take full ownership of having made a bad design decision).

In this page, I display images from a database (a PDF preview thumb nail) - not a large image.

However, since the images are "in line" base64 images (no URL links to the image), and the image is in fact a asp.net "image button"?

Well, that means when users click on the button, then the page is posted back to the server. And since those images can't be cached by the browser, then all of those images are posted back to the server on button clicks. This is a "poor" design on my part. During testing, I only had about 5 or maybe 10 images tops. But, some clients used that page with 50, or 100 images!!

Well, that quite much causes a browser freeze up --- and customers get a VERY long browser spinner if they have a less then ideal internet speed....

Now, I was (and in fact had) changed the button code from a post-back of the page to that of a web method call (I changed button behavior to use ajax). But, as I started writing this code to fix the issue?

I realised I did't really have to change the code - what I had to do was tell asp.net to NOT perserve the image setting for that button (automatic view state!). By a simple turning off of viewstate for the button, then the delay, and "long waiting browser spinner" was all but eliminated....

In other words, this freeze-up like behavior was my fault, and the result of a poor design decision......

It don't matter if you using Access or doing web design - the problems in most cases are near always the fault of the developer! (that's me!!!!!).

All you have to do is fix a few of the poor designs, and for the most part is actually quite difficult to "kill" Access and freeze it up!

Access can actually tolerate quite a few bad design choices. Hence, in this example, use of on-open was the issue. Sure, you inherited some "less" then ideal dlookups(), but at the end of the day, even with those not so great dlookups(), the freeze up would have not occurred, or could be eliminated by adding some doEvents - and existing code not even changed. The wrong event was being used.

We often see the same in a access form when after update is used when before update should have been used....

Access applications don't and should not freeze up very often - and when they do, in most cases it means the developer(s) did something that should not have been done.....

So, no, there no magic fix - but in this case, and my web example? The problem was the developer bad choices, and not the tools being used....

R
Albert