Function with DoCmd.OpenForm and then return to function (1 Viewer)

[adhoustonj]

Hello AWF,
I'm experiencing a problem with a function - UpdateData() in my db, and hoping someone can tell me if I am missing something.
I have an update procedure that runs each morning, and it usually takes 20-30 minutes. Recently it has been getting hung up and taking over an hour, and sometimes not even completing. Usually the db becomes unresponsive and locks up while this is happening - expected.

But, my backend is growing very large from this - sometimes to the 2gb limit, and once I compact and repair, then it goes back to 70-80mb.

This function updates data with a bunch of SQL strings, and db.execute - then it performs a check, if models need a model group assigned, opened the model_no_grp form -

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
    DoCmd.OpenForm "frmModels_no_grp"
    Exit Function
End If

And then after assigning models, there is a continue with updates button at the top of the screen, which calls the original function again, and will execute the SQL strings, and perform the check again - but now models have been assigned, and the form will not open, it will just continue to run through the entire function.

Private Sub cmd_Continue_Updates_Click()
UpdateData
DoCmd.Close
End Sub

Is the Exit Function ever getting processed if the frmModels_no_grp is opened, and then the cmd_Continue_Updates is clicked which runs the function again?

I feel as though there is some function-ception (inception movie) going on, and I'm bloating my back end with.. I dont even know what, or how, but when it cycles through the entire function without opening the form, I do not get the back end bloat.

I've used a database_table_documenter function that exports each table to a txt file, and the size of the tables are changing a few kb, even when the backend changes from 70-80mb to 2gb.

Any thoughts?

I can post the entire function if needed.

 

[MajP]

I think you have a couple of issues

But, my backend is growing very large from this - sometimes to the 2gb limit, and once I compact and repair, then it goes back to 70-80mb.

If you are doing a lot of adding and deleting and use of temp tables this can cause a lot of bloating. Even if you only end up with a few final records. There are techniques especially for using temp tables. Need to see a lot more code to help

I can post the entire function if needed.

Yes.

I do not understand the rest of what you are asking but two things to maybe look at and understand

DoEvents function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

When you do this

DoCmd.OpenForm "frmModels_no_grp"
Exit Function

The called form opens and runs through its event procedures. As soon as complete the code in the calling function resumes. If instead you used the ACDIALOG windowmode argument then code execution will not resume in the calling form until the called form is closed. You did not do this however.

 

[adhoustonj]

I do use temp tables in this update function. This is the function below.

Option Compare Database
Dim db As DAO.Database
Dim rs As DAO.Recordset, rs2 As DAO.Recordset
Dim strSQL As String, strSQL2 As String
Global seqNumber As Long
Global lastcall As Date
Function UpdateData()
If gcfHandleErrors Then On Error GoTo Err_regulate_function
Dim mo As String, eu As String
Dim Rev As LongPtr, ct As LongPtr

Set db = CurrentDb()

'delete temp table
strSQL = "DELETE tblReg_Models_up.* " _
& "FROM tblReg_Models_up"
db.Execute strSQL

strSQL = "DELETE tblReg_Models_temp.* " _
& "FROM tblReg_Models_temp"
db.Execute strSQL

'insert new models
strSQL = "INSERT INTO tblReg_Models ( model, begdate ) " _
& "SELECT bm.model, Date() AS dt " _
& "FROM (SELECT DISTINCT Left([eu_itemno],4) AS model " _
& "FROM [" & isc & "].Dbo.custom_pymac_bom as cpb " _
& "WHERE (((Left([eu_itemno],4)) Not Like ""J*"" And (Left([eu_itemno],4)) Not Like ""F*"") AND ((cpb.start_date)>CStr(Format(Date()-15,""yyyymmdd"")))))  AS bm " _
& "LEFT JOIN tblReg_Models ON bm.model = tblReg_Models.model " _
& "WHERE (((tblReg_Models.model) Is Null) AND ((Right([bm]![model],1)) Not Like ""x""))"
db.Execute strSQL

'update the model group on the new models
strSQL = "INSERT INTO tblReg_Models_up ( 3m, mdl_grp_id ) " _
& "SELECT DISTINCT Left([model],3) AS 3m, tblReg_Models.mdl_grp_id " _
& "FROM tblReg_Models " _
& "WHERE (((tblReg_Models.mdl_grp_id) Is Not Null))"
db.Execute strSQL

strSQL = "UPDATE (SELECT tblReg_Models.model, tblReg_Models.mdl_grp_id, Left([model],3) AS 3m " _
& "FROM tblReg_Models " _
& "WHERE (((tblReg_Models.mdl_grp_id) Is Null)))  AS rm INNER JOIN tblReg_Models_up ON rm.[3m] = tblReg_Models_up.[3m] SET rm.mdl_grp_id = [tblReg_Models_up]![mdl_grp_id]"
db.Execute strSQL

strSQL = "DELETE tblReg_Models_up.* " _
& "FROM tblReg_Models_up"
db.Execute strSQL

'if new models no model grp open model form
strSQL = "SELECT tblReg_Models.mdl_grp_id, tblReg_Models.model, tblReg_Models.begdate, tblReg_Models.obsolete, tblModel_Grp.hs_area_id " _
& "FROM tblModel_Grp RIGHT JOIN tblReg_Models ON tblModel_Grp.mdl_grp_id = tblReg_Models.mdl_grp_id " _
& "WHERE (((tblReg_Models.mdl_grp_id) Is Null))"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
    DoCmd.OpenForm "frmModels_no_grp"
    Exit Function
End If

'insert models from bom less than 16 days old into temp table
strSQL = "INSERT INTO tblReg_Models_temp ( model, eu_itemno, rev ) " _
& "SELECT p.model, p.eu_itemno, IIf([r] Is Null,0,[r]) AS rev FROM (SELECT DISTINCT Left([eu_itemno],4) AS model, First(cpb.eu_itemno) AS eu_itemno " _
& "FROM [" & isc & "].dbo.custom_pymac_bom as cpb " _
& "WHERE(((cpb.start_date) > CStr(Format(Date() - 15, ""yyyymmdd""))) And ((Len(RTrim([eu_itemno]))) = 10)) " _
& "GROUP BY Left([eu_itemno],4) " _
& "HAVING (((Left([eu_itemno],4)) Not Like ""J*"" And (Left([eu_itemno],4)) Not Like ""F*"")))  AS p LEFT JOIN (SELECT DISTINCT tblReg_Models_revs.model, Max(tblReg_Models_revs.Rev) AS r FROM tblReg_Models_revs GROUP BY tblReg_Models_revs.model)  AS mr ON p.model = mr.model"
db.Execute strSQL

'set up tblpymac_local
strSQL = "DELETE tblpymac_local.* " _
& "FROM tblpymac_local"
db.Execute strSQL

strSQL = "INSERT INTO tblpymac_local ( eu_itemno, itemno, nak, pn, start_date, not_use ) " _
& "SELECT distinct qryPymac_bom.eu_itemno, qryPymac_bom.itemno, qryPymac_bom.nak, qryPymac_bom.pn, qryPymac_bom.start_date, tblReg_pn.not_use " _
& "FROM ((tblReg_nakago INNER JOIN qryPymac_bom ON tblReg_nakago.nak = qryPymac_bom.nak) INNER JOIN tblReg_Models_temp ON qryPymac_bom.eu_itemno = tblReg_Models_temp.eu_itemno) LEFT JOIN tblReg_pn ON qryPymac_bom.pn = tblReg_pn.pn " _
& "WHERE (((qryPymac_bom.start_date)>CStr(Format(Date()-15,""yyyymmdd""))))"
db.Execute strSQL

'add new parts for child parts
strSQL = "INSERT INTO tblReg_child_pn ( py_pn) SELECT distinct np.pn " _
& "FROM (SELECT qryPymac_bom.pn FROM (tblReg_nakago INNER JOIN qryPymac_bom ON tblReg_nakago.nak = qryPymac_bom.nak) INNER JOIN tblReg_Models_temp ON qryPymac_bom.eu_itemno = tblReg_Models_temp.eu_itemno " _
& "WHERE (((tblReg_nakago.child)=1)))  AS np LEFT JOIN tblReg_child_pn ON np.pn = tblReg_child_pn.py_pn " _
& "WHERE (((tblReg_child_pn.py_pn) Is Null))"
db.Execute strSQL

'cycle models to see if need new reg or rev up
strSQL = "SELECT tblReg_Models_temp.model, tblReg_Models_temp.eu_itemno, tblReg_Models_temp.rev " _
& "FROM tblReg_Models_temp"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    While Not rs.EOF
        mo = rs.Fields("model")
        eu = rs.Fields("eu_itemno")
        Rev = rs.Fields("rev")
           
        'insert new parts from bom into tblReg_pn
        strSQL2 = "INSERT INTO tblReg_pn ( pn, nak_id ) " _
        & "SELECT np.pn, np.nak_id " _
        & "FROM (SELECT qryPymac_bom.pn, tblReg_nakago.nak_id " _
        & "FROM (tblReg_nakago INNER JOIN qryPymac_bom ON tblReg_nakago.nak = qryPymac_bom.nak) INNER JOIN tblReg_Models_temp ON qryPymac_bom.eu_itemno = tblReg_Models_temp.eu_itemno " _
        & "WHERE (((tblReg_Models_temp.model)='" & mo & "') AND ((tblReg_nakago.child) <1)))  AS np LEFT JOIN tblReg_pn ON np.pn = tblReg_pn.pn " _
        & "WHERE (((tblReg_pn.pn) Is Null))"
        db.Execute strSQL2
       
        'if rev does not exist then create 1st rev
        If Rev = 0 Then
            strSQL2 = "INSERT INTO tblReg_Models_revs ( model, Rev, Date_Issued, Issued_By, reason ) " _
            & "values ('" & mo & "', 1, #" & Date & "#, 1, ""New sheet"")"
            db.Execute strSQL2
        Else
            'see if new rev is needed
            strSQL2 = "SELECT py.pn " _
            & "FROM (SELECT tblpymac_local.pn, tblReg_Models_temp.model  FROM (tblpymac_local  INNER JOIN  tblReg_Models_temp  ON tblpymac_local.eu_itemno = tblReg_Models_temp.eu_itemno)  INNER JOIN tblReg_nakago  ON tblpymac_local.nak = tblReg_nakago.nak  WHERE (((tblReg_nakago.child)<1) AND ((tblpymac_local.not_use)=False)) " _
            & "union  " _
            & "SELECT DISTINCT tblReg_pn.pn, tblReg_Models_temp.model FROM  ((SELECT tblReg_nakago.nak_id, tblReg_nakago.nak, tblReg_child_pn.py_pn, tblReg_child_pn.pn_id FROM tblReg_nakago  INNER JOIN tblReg_child_pn  ON tblReg_nakago.nak_id = tblReg_child_pn.nak_id WHERE (((tblReg_nakago.child)<1) AND ((tblReg_child_pn.disreqard)=No)))  AS n  " _
            & "INNER JOIN (tblReg_Models_temp  INNER JOIN qryPymac_bom  ON tblReg_Models_temp.eu_itemno = qryPymac_bom.eu_itemno)  ON n.py_pn = qryPymac_bom.pn)  INNER JOIN tblReg_pn  ON n.pn_id = tblReg_pn.pn_id)  AS py LEFT JOIN (SELECT tblReg_pn.pn, tblReg_Models_temp.model FROM (tblReg_Models_temp INNER JOIN (tblReg_Models_revs INNER JOIN tblReg_masters ON tblReg_Models_revs.rec_mod_id = tblReg_masters.rec_mod_id) " _
            & "ON (tblReg_Models_temp.model = tblReg_Models_revs.model) AND (tblReg_Models_temp.rev = tblReg_Models_revs.Rev)) INNER JOIN tblReg_pn ON tblReg_masters.pn_id = tblReg_pn.pn_id WHERE (((tblReg_pn.not_use)=No)))  AS old ON (py.pn = old.pn) AND (py.model = old.model) " _
            & "WHERE (((py.model)='" & mo & "') AND ((old.pn) Is Null) AND ((old.model) Is Null))"
           
            Set rs2 = db.OpenRecordset(strSQL2)
            ct = rs2.RecordCount
           
            strSQL2 = "SELECT old.pn " _
            & "FROM (SELECT tblpymac_local.pn, tblReg_Models_temp.model  FROM (tblpymac_local  INNER JOIN  tblReg_Models_temp  ON tblpymac_local.eu_itemno = tblReg_Models_temp.eu_itemno)  INNER JOIN tblReg_nakago  ON tblpymac_local.nak = tblReg_nakago.nak  WHERE (((tblReg_nakago.child)<1) AND ((tblpymac_local.not_use)=False)) " _
            & "union " _
            & "SELECT DISTINCT tblReg_pn.pn, tblReg_Models_temp.model FROM  ((SELECT tblReg_nakago.nak_id, tblReg_nakago.nak, tblReg_child_pn.py_pn, tblReg_child_pn.pn_id FROM tblReg_nakago  INNER JOIN tblReg_child_pn  ON tblReg_nakago.nak_id = tblReg_child_pn.nak_id WHERE (((tblReg_nakago.child)<1) AND ((tblReg_child_pn.disreqard)=No)))  AS n  " _
            & "INNER JOIN (tblReg_Models_temp  INNER JOIN qryPymac_bom  ON tblReg_Models_temp.eu_itemno = qryPymac_bom.eu_itemno)  ON n.py_pn = qryPymac_bom.pn)  INNER JOIN tblReg_pn  ON n.pn_id = tblReg_pn.pn_id)  AS py RIGHT JOIN (SELECT tblReg_pn.pn, tblReg_Models_temp.model FROM (tblReg_Models_temp INNER JOIN (tblReg_Models_revs INNER JOIN tblReg_masters ON tblReg_Models_revs.rec_mod_id = tblReg_masters.rec_mod_id) " _
            & "ON (tblReg_Models_temp.model = tblReg_Models_revs.model) AND (tblReg_Models_temp.rev = tblReg_Models_revs.Rev)) INNER JOIN tblReg_pn ON tblReg_masters.pn_id = tblReg_pn.pn_id WHERE (((tblReg_pn.not_use)=No)))  AS old ON (py.pn = old.pn) AND (py.model = old.model) " _
            & "WHERE (((py.pn) Is Null) AND ((py.model) Is Null) AND ((old.model)='" & mo & "'))"
           
            Set rs2 = db.OpenRecordset(strSQL2)
            ct = ct + rs2.RecordCount
           
            'if is needed add new rev and insert parts
            If ct > 0 Then
                'insert new model into new models rev
                strSQL2 = "INSERT INTO tblReg_Models_revs ( model, Rev, Date_Issued, Issued_By, reason ) " _
                & "values ('" & mo & "', " & Rev + 1 & " , #" & Date & "#, 1, ""Automated New rev"")"
                db.Execute strSQL2
            End If
        End If

        rs.MoveNext
    Wend
End If
strSQL = "DELETE tblReg_Models_temp.* " _
& "FROM tblReg_Models_temp"
db.Execute strSQL

strSQL = "DELETE tblpymac_local.* " _
& "FROM tblpymac_local"
db.Execute strSQL
rs.Close
Set rs = Nothing
Set db = Nothing

Exit_regulate_function:
Exit Function
Err_regulate_function:
Call LogError(err.Number, err.Description, "regulate_function()")
Resume Exit_regulate_function
End Function

 

[adhoustonj]

I had to split this up into 2 messages.

if the frmModels_no_grp is opened due to needing to assign models, then the code on that form is -

Option Compare Database

Private Sub Closepage_Click()
DoCmd.Close
End Sub


Private Sub cmd_Continue_Updates_Click()
UpdateData
DoCmd.Close
End Sub

So in the function when this below is called -

DoCmd.OpenForm "frmModels_no_grp"
Exit Function

- the form opens, and models are assigned, and then I click the continue_updates button, and it calls the function all over again - I'm questioning whether the first Exit Function after opening the form is ever processed, or hung up in the background/bloat, as I run the function all over again by pressing continue_updates.

 

[adhoustonj]

So it sounds like a better way to handle it would be to remove the UpdataData call below on the frmModels_no_grp, and instead just open the frmModels_no_grp with ACDIALOG, update the models, and then just close that form, and it will continue with the original function call, instead of calling it over again.

Private Sub cmd_Continue_Updates_Click()
UpdateData
DoCmd.Close
End Sub

 

[MajP]

Here is one method (side database) that may help. I did not have time to read your response though.

Solved - Does It Affect My DB in Any Way If I Delete Temporary Tables after Creating and Using?

Hello, I need to create a temp table for my one subroutine to work. I have a sub on form load even which checks if temp table exists or not. If it doesn't, then it creates it. Should I delete the temp table while closing the form or let it remain for next time usage? Will creating and then...

www.access-programmers.co.uk

 

[adhoustonj]

Here is one method (side database) that may help. I did not have time to read your response though.

Solved - Does It Affect My DB in Any Way If I Delete Temporary Tables after Creating and Using?

Hello, I need to create a temp table for my one subroutine to work. I have a sub on form load even which checks if temp table exists or not. If it doesn't, then it creates it. Should I delete the temp table while closing the form or let it remain for next time usage? Will creating and then...

www.access-programmers.co.uk

That is great. I will look into the side ends also! Thank you.

I will add though - my bloat happens in the backend, and all temp tables are in the front end, which does not experience the bloat.

 

[Pat Hartman]

There is something logically wrong with having to open a form to perform a batch process. Forms are intended to interact with humans. If you want to perform the same function from a form and from a batch process, put the code into a standard code module and call it from the form as well as from the batch process.

There is no need to open a recordset - and leave it open - to check to see if there are any relevant records there. Just use a dCount(). It has less overhead in this situation.

 

[adhoustonj]

There is something logically wrong with having to open a form to perform a batch process. Forms are intended to interact with humans. If you want to perform the same function from a form and from a batch process, put the code into a standard code module and call it from the form as well as from the batch process.

There is no need to open a recordset - and leave it open - to check to see if there are any relevant records there. Just use a dCount(). It has less overhead in this situation.

I will look to move to dCount instead!

This is in a standard code module now. And I am calling the function from the batch process & the form. I am not sure if the logic is okay though. Below are current steps.

1. Batch process runs and calls UpdateData
2. Form needs to open to assign model groups because there are none assigned

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
    DoCmd.OpenForm "frmModels_no_grp"
    Exit Function
End If

3. After updating the form, button is clicked on form that calls UpdateData
4. Form does not need to open because model groups have been assigned
5. UpdateData completes

I am questioning the "Exit Function" in step 2 - would this ever be computed, since I am calling the UpdateData from inside the initial UpdateData call?

 

[Pat Hartman]

And I am calling the function from the batch process & the form.

You are opening the form from the batch process. That is what doesn't make sense. Forms are interactive. If you are opening the form so you can call code in the form's class module, you should move the code from the form's class module to a standard module and call the common procedure from both the form and the batch procedure.

After updating the form

are YOU opening the form in the middle of the night? Is the form running update code when it opens? That would be really poor.

since I am calling the UpdateData from inside the initial UpdateData call?

Recursive calls are quite dangerous if you don't know what you are doing and do not have a way to exit the loop gracefully. If your app is going into a tight loop, i.e. locking up, this code is probably the culprit. Some data situation is keeping the loop from ending correctly.

 

[adhoustonj]

You are opening the form from the batch process. That is what doesn't make sense. Forms are interactive. If you are opening the form so you can call code in the form's class module, you should move the code from the form's class module to a standard module and call the common procedure from both the form and the batch procedure.

are YOU opening the form in the middle of the night? Is the form running update code when it opens? That would be really poor.

Recursive calls are quite dangerous if you don't know what you are doing and do not have a way to exit the loop gracefully. If your app is going into a tight loop, i.e. locking up, this code is probably the culprit. Some data situation is keeping the loop from ending correctly.

I'm thinking the same. Okay, I need to look into separating this update process.

The batch process runs by 1 specific admin user that comes in earlier, and the purpose of the form opening while update process happens is essentially a filter. We are updating BOM/MRP data, but not bringing all of the data over into the db. If a new model is added that day, then we need to assign a model group to insert into db and then have the update process include that model.

Thanks for your words of wisdom.

 

[Pat Hartman]

Then you need a query that determines if a new model was added the day before (or the friday before or whatever) That query should drive the process.

Not sure why this isn't done initially when the new model is added unless the data is added to the server via some other process and you need to pick it up later.

It sounds like you are having a human control the process and that is why you are opening forms. There is always a better way unless a human actually needs to make a decision at this point.

Rather than having to rely on someone remembering to run the process, if you have a local PC that you can leave on overnight, you can use Windows scheduler to open the database and run the batch process. You would create a special macro to kick it off and the Batch job would open the database with the macro parameter so your "batch" macro would start. You can include a final step that logs the results. That way, when the database opens the next day, it can look at the log and notify you if the process didn't run. The macro does nothing except start the batch process. Everything else is in VBA and as the very last step of the process, you need to close the database.