UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Calling VBA Function Extremely Slow - Please Help, Access 2016    
 
   
archie1_za
post Nov 28 2017, 08:10 AM
Post#1



Posts: 8
Joined: 28-November 17



Hi There

I would like to state upfront I am a newbie and total novice, so please excuse me if I dont totally make sense wary.gif

I have used the fNetWorkdays() & fAddWorkdays() found on the forums. They work well.


However when I run the query and try to view the results in datasheet view, the entire system/database literally just hangs. If I try to scroll to view results Access just hangs and I have to kill it and restart. It seems that the function is continuously running through every field in every record. I just have 10 test records and will eventually have over 7000 records. Any ideas as to how I can speed this up or how I can avoid this issue.

I have also attached a text file with a function similar to fNetWorkdays() & fAddWorkdays() that I have used and the result is the same.

Is my design really bad or even my SQL badly coded?

Any suggestions would be greatly welcomed. I am really stuck and dont know how to move forward. If needs be I am prepared to start from scratch again.

Thanking you in advance
P.S The DB is attached as well.
Attached File(s)
Attached File  DB.zip ( 1.45MB )Number of downloads: 8
 
Go to the top of the page
 
archie1_za
post Nov 29 2017, 12:53 AM
Post#2



Posts: 8
Joined: 28-November 17



No Assistance ?? confused.gif
Go to the top of the page
 
projecttoday
post Nov 29 2017, 01:30 AM
Post#3


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


All I got when I tried to run that query is "Query is too Complex". I am using Access 2010.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
archie1_za
post Nov 29 2017, 01:34 AM
Post#4



Posts: 8
Joined: 28-November 17



If that happens, which sometimes happened to me, I just removed the following from the query (or leave just the first line of the code below) :
I think its as a result of too many statements within the SELECT statement

IIf(PlanFinConfgCrStartDate Is Not Null And FinConfigCrStAD Is Not Null,Calcdays(PlanFinConfgCrStartDate,FinConfigCrStAD),0) AS FinConfigCrDaysDelay,
IIf(PlanFinConfgLdComDate Is Not Null And FinConfigLdComAD Is Not Null,Calcdays(PlanFinConfgLdComDate,FinConfigLdComAD),0) AS FinConfigLdCDaysDelay,
IIf(PlanServConfgCrComDate Is Not Null And ServConfgCrComAD Is Not Null,Calcdays(PlanServConfgCrComDate,ServConfgCrComAD),0) AS ServConfgCrDaysDelay,
IIf(PlanCrPBATPComDate Is Not Null And CrPBATPComAD Is Not Null,Calcdays(PlanCrPBATPComDate,CrPBATPComAD),0) AS CrPBATPDaysDelay,
IIf(PlanNIpamRqComDate Is Not Null And nIpamRqComAD Is Not Null,Calcdays(PlanNIpamRqComDate,nIpamRqComAD),0) AS nIpamRqDaysDelay,
IIf(PlanNIpamRxStartDate Is Not Null And nIpamRxComAD Is Not Null,Calcdays(PlanNIpamRxStartDate,nIpamRxComAD),0) AS nIpamRxDaysDela
Go to the top of the page
 
projecttoday
post Nov 29 2017, 07:10 AM
Post#5


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


I took that part of the code out and still got "Query is too complex". Which I think is the whole problem here. One of these functions has a Dcount in it. That alone can you cause a problem. Where is the CalcDays function?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
River59
post Nov 29 2017, 09:42 AM
Post#6



Posts: 1,345
Joined: 7-April 10
From: Detroit, MI


This query does runs for me (a bit hinky, but it runs). If I try to scroll in the query, it will 'hang up' but still completes. I just tried making a table from it and that ran fine. No sure where the problem is but will keep putzing with it to see if I can pinpoint what is going on.

Should note that I am running Office 2016 32-bit on 64-bit laptop.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
River59
post Nov 29 2017, 10:37 AM
Post#7



Posts: 1,345
Joined: 7-April 10
From: Detroit, MI


I put the query in a module and ran it as a make table query. It still took a few seconds to run. The query is correct but maybe you need to find a way to break it apart into steps. Sorry I couldn't be of more help.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
archie1_za
post Nov 30 2017, 01:58 AM
Post#8



Posts: 8
Joined: 28-November 17



@ Projecttoday

Hello smile.gif . The calcDays function is included in the txt file that I uploaded.

Do you guys/gals think that I should rather populate a table with lets say the next 3 years with of dates. Mark each Saturday/Sunday/Public Holiday with a 0 and every other day with a 1.

Somehow write SQL code to read it or maybe another function?

What would the function look like and how would I call it?
Go to the top of the page
 
projecttoday
post Nov 30 2017, 02:14 AM
Post#9


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


For the holidays only, since the Weekday function will return Saturday and Sunday. I don't see the calcdays function in the Functions module. What is it that you want this query to do?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
projecttoday
post Nov 30 2017, 02:18 AM
Post#10


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


Okay, I see it in the text file.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
archie1_za
post Nov 30 2017, 08:03 AM
Post#11



Posts: 8
Joined: 28-November 17



To everyone that has helped.

I would like to sincerely apologise as it has been brought to my attention that I cross posted on 1 other forum. I have subsequently been schooled in the etiquette of forums and will definitely abide by the advice given. It was genuinely not my intention to waste anyone's time and effort. Once again I sincerely apologise.
Go to the top of the page
 
River59
post Nov 30 2017, 08:26 AM
Post#12



Posts: 1,345
Joined: 7-April 10
From: Detroit, MI


Sometimes that happens, Archie. If you do find an answer somewhere else, it would be nice if you posted here so others can learn.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
projecttoday
post Nov 30 2017, 09:05 AM
Post#13


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


I don't mind helping if I can. What is it that you want this query to do?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
archie1_za
post Nov 30 2017, 09:23 AM
Post#14



Posts: 8
Joined: 28-November 17



The query has a few fixed dates in a table. The rest are calculated dates. This will eventually be in a form where the user will enter an initial (PO) date. It will then calculate all the planned dates and show it to the user. As they move along the tasks, they will select the relevant PO number from a drop down list and it will fill in the rest of the form, where as they complete a task they can enter the "fixed" dates which will recalculate some of the fields and give them an overview of where they are as far as what the plan dates are. I hope that makes sense. If there is an easier way to do this , I'm open to all ideas.

I would not have had the problem of the query being so slow if I didn't need to exclude weekends and public holidays in my calculations.

Edit - This query would eventually become a subform where when the user selects a specific project (with a unique ID which will be linked to the PO no and date in the PO table), it will populate the relevant info in the subform. I would obviously do all the table relations etc.

The query fields will be the same for all the projects. Again, if there is an easier/more efficient way, please do share.
Go to the top of the page
 
projecttoday
post Nov 30 2017, 09:36 AM
Post#15


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


If you're satisfied except for the execution time, have you isolated which function(s) is/are causing the problem? As I said earlier, Dcount is questionable.

If you want to explore better ways, walk us through this with an example. It sounds complicated.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
archie1_za
post Nov 30 2017, 10:06 AM
Post#16



Posts: 8
Joined: 28-November 17



We have various projects that have a Project Open Date (PO date). According to the company each task in the project must be completed within a certain number of days i.e. the planned dates. As we know nothing ever goes to plan so we must capture the actual dates for each task. Start date and Complete date.

So for example: Project IronMan is made up of 4 tasks:

1.PlanTheSuit
2.BuildTheSuit
3.TestTheSuit
4.WearTheSuit


So From The PO Date for Project IronMan "PlanTheSuit" must start after 3 days.

So we have PODate + 3 = PlannedPlanTheSuitStartdate

The planning of the suit must be completed on the same day its starts
So PlannedPlanTheSuitStartdate + 0 = PlannedPlanTheSuitCompleteDate

In a Table I have two fields , ActualPlanTheSuitStartDate & ActualPlanTheSuitCompleteDate. The users will fill this in the form to keep record of what is actually happening.

I then calculate the following using the current Date() ....I look at the PlannedPlanTheSuitStartdate and if it has lapsed then a status of "Delayed" will show. There are various status depending on what the current date is.

I then look at the PlannedPlanTheSuitCompleteDate & ActualPlanTheSuitCompleteDate and work out how many days the task has been delayed by, if any.

Then I move onto BuildTheSuit which is PO Date + 4 ...and so it continues until all the tasks are complete.


I will eventually load all the projects ... Ironman, Superman, Batman etc..
The user will then select the project from a combo box, if there is no PO date , he/she will enter it and start the project. If they select a project they have already started, it will populate the fields where they can enter their actual dates for each task.


I hope this makes sense....
Go to the top of the page
 
projecttoday
post Nov 30 2017, 10:19 AM
Post#17


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


Planthesuitstartdate? You could have design problems. Is suit planning 1 of several possible projects?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
archie1_za
post Dec 1 2017, 12:40 AM
Post#18



Posts: 8
Joined: 28-November 17



Hi Guys

Thank You For All The wonderful help and assistance. I sat until the wee hours and finally went this route:

I used my existing queries to create a table, too lazy to go create them myself!! Once done I updated my SQL with Insert & Update Queries and "sub queries". They work perfectly and the speed is excellent. I still use the date functions within my Updates but have researched that my SQL wasn't the most efficient, so I re-wrote some of it.

Basically the solution was to have the query write to a table, and then a sub query to update it when changes were made.

I don't know if that makes sense - but WOOHOOO!!! I have something that works and is very fast.

Thanks Guys
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 07:12 PM