After update event on field Takes too long

[tucker61]

I have a after update event on a field that takes approx 15 - 20 seconds to run, The event code is below.

Is there any way to let the code run in the background without the user having to wait for this code to run ?

I used to run this as a append query overnight, but have had to stop that process.

I think the delay is down to the last 2 fields, which are in different tables. Cost price and Direct Price. If i remove these, the code runs in approx 5 seconds.

Any ideas welcome.

WarningsOff
strsql = "INSERT INTO tblQCProduct ( Line_No, Division, Department, Category, Sub_Category, Date_Added, Line_Description, Landed_cost, Direct_Price ) " & vbCrLf & _
"SELECT DISTINCT tblcatalog.Cat_No, tblcatalog.Division, tblcatalog.Department, tblcatalog.Category, tblcatalog.Sub_Category, Now() AS Date_Added, tblproducts.Line_Description, Max(tblcostprice.Cost_Price) AS MaxOfCost_Price, Max(tblDirectPrice.Direct_Price) AS MaxOfDirect_Price " & vbCrLf & _
"FROM ((tblcatalog INNER JOIN tblproducts ON tblcatalog.Cat_No = tblproducts.Cat_No) LEFT JOIN tblcostprice ON tblproducts.Option_No = tblcostprice.Option_No) LEFT JOIN tblDirectPrice ON tblproducts.Option_No = tblDirectPrice.Option_No " & vbCrLf & _
"GROUP BY tblcatalog.Cat_No, tblcatalog.Division, tblcatalog.Department, tblcatalog.Category, tblcatalog.Sub_Category, Now(), tblproducts.Line_Description " & vbCrLf & _
"HAVING (((tblcatalog.Cat_No)=[Forms]![frmMain]![tbCatNo]));"
Debug.Print strsql
DoCmd.RunSQL strsql
WarningsOn

 

[Ranman256]

no, access is not multi threaded.
you must wait.

 

[Minty]

Can you rewrite that to move the having clause inside as a join?
At the moment it is having to collect the entire datatset then check that against the Having clause.
It will work much quicker if you can apply it as a join inside the query.

 

[Mark_]

Can you gather your values PRIOR to doing the insert? Then you would only need to do the INSERT without all of the other assorted processing.

I am guessing if you can pre-fill these values (Showing them to the users may also be useful) then your processing time will be reduced dramatically. May also mean that what you are currently doing can simply be done by ACCESS itself without the need for you to handle the update yourself.

 

[tucker61]

Thanks for the replies guys, unfortunately I can't capture the information before the update event, as the info depends on the update.

Came across another issue late on, the line description fields that is being imported sometimes contains apostrophes, so i might have to run this as a query otherwise I think I will get Sql errors..

Could never get my head around if it is quicker to write the code as above or just to run a query. I thought it might be quicker to do as above, what do you guys think ?

Sent from my SM-T715 using Tapatalk

 

[Mark_]

From the look of it, I am guessing you select a product then use its catalog number to drive everything. As you are entering a description, I would also hazard a guess that you do so AFTER selecting the item.

For me, I would add text boxes on the screen to show your CostPrice and DirectPrice and in the after accept for what ever gets you your tblcatalog.Can_No, I would put the following:

DIM OptNo as Integer
OptNo = DLookup( "Option_No","tblproducts", "tblproducts.Cat_No = " & tblcatalog.Cat_No )
Me.Txt_CostPrice = DMax("Cost_Price", "tblcostprice", "tblcostprice.Option_No = " & OptNo )
Me.Txt_DirectPrice = DMax("Direct_Price", "tblDirectPrice", "tblDirectPrice.Option_No = " & OptNo )

Hopefully this would cut down the processing time as you would be able to reference the display on the screen rather than embedding the processing into your query.

 

[Pat Hartman]

1. HAVING and WHERE are NOT interchangeable. WHERE is used when you want to apply criteria before aggregating the data. HAVING is applied after aggregation and always references an aggregated value such as a sum, average, or count, not a value from the base data. Therefore, making the HAVING a WHERE will reduce the number of rows selected to be aggregated. Depending on the size of the recordset, this may or may not speed it up.
2. It is almost never necessary to duplicate data and that is what your query is doing.

 

[tucker61]

From the look of it, I am guessing you select a product then use its catalog number to drive everything. As you are entering a description, I would also hazard a guess that you do so AFTER selecting the item.

For me, I would add text boxes on the screen to show your CostPrice and DirectPrice and in the after accept for what ever gets you your tblcatalog.Can_No, I would put the following:

DIM OptNo as Integer
OptNo = DLookup( "Option_No","tblproducts", "tblproducts.Cat_No = " & tblcatalog.Cat_No )
Me.Txt_CostPrice = DMax("Cost_Price", "tblcostprice", "tblcostprice.Option_No = " & OptNo )
Me.Txt_DirectPrice = DMax("Direct_Price", "tblDirectPrice", "tblDirectPrice.Option_No = " & OptNo )

Hopefully this would cut down the processing time as you would be able to reference the display on the screen rather than embedding the processing into your query.

This really helped me out, I realised that the cost price was already being pulled into a text field on the form, so instead of recreating the join in vba I amended the code to pull the value from the text field. I also added the direct price to the form at the same time..

The other fields I need are also on the form, so i will probably change all the code to look at the form instead of looking in the tables for all the fields..

Sent from my SM-T715 using Tapatalk

 

[MarkK]

To me it looks like a table design problem that you should need to do such an update at all. What is a QCProduct? In your SQL you are SELECTing DISTINCT from tables like Catalog. Product and two Price tables, and then saving the record denormalized. What do you do with tQCProduct? Wouldn't it always be simpler to just save this query...

"SELECT DISTINCT tblcatalog.Cat_No, tblcatalog.Division, tblcatalog.Department, tblcatalog.Category, tblcatalog.Sub_Category, Now() AS Date_Added, tblproducts.Line_Description, Max(tblcostprice.Cost_Price) AS MaxOfCost_Price, Max(tblDirectPrice.Direct_Price) AS MaxOfDirect_Price " & vbCrLf & _
"FROM ((tblcatalog INNER JOIN tblproducts ON tblcatalog.Cat_No = tblproducts.Cat_No) LEFT JOIN tblcostprice ON tblproducts.Option_No = tblcostprice.Option_No) LEFT JOIN tblDirectPrice ON tblproducts.Option_No = tblDirectPrice.Option_No " & vbCrLf & _
"GROUP BY tblcatalog.Cat_No, tblcatalog.Division, tblcatalog.Department, tblcatalog.Category, tblcatalog.Sub_Category, Now(), tblproducts.Line_Description "

...and use it instead? Why copy that data to a table when you can just query it from its source? Or, if this is a kind of audit (since I see you are saving the value of VBA.Now()) then wouldn't it be sufficient to just save the Cat_No? Why re-save all those fields from tblCatalog, when, presumably, they still exist in tblCatalog?
hth
Mark

 

[jdraw]

tucker,

As others have suggested, it might be helpful to readers and you if you would tell us more about what you are trying to accomplish in plain English --not SQL nor vba.

 

[tucker61]

i think i have sussed it due to the above, the code runs on a afterupate event on the TBCatNo field. This copies the data from one table (Which holds live information) into a second table (TblQCProducts) which i have created to archive the data.