Fast 5th Generation SSD Transfer Rate (1 Viewer)

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