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
> Vba Code Slow, Access 2010    
 
   
MSNewb4865
post May 17 2019, 03:41 PM
Post#1



Posts: 17
Joined: 6-March 19



I have an interesting dilemma happening here. If I run my queries manually, they run very quickly. However once I group them into a private sub run off a button, it takes 30 minutes for the update!! How do I fix this??

Code is embedded into a form.

Private Sub CmdUpdateTemp_Click()
DoCmd.RunCommand acCmdSave
DoCmd.SetWarnings False
DoCmd.OpenQuery “qryExcPDOCOverview”
DoCmd.OpenQuery “qryExcPNDetails”
DoCmd.OpenQuery “qryExcPNDetailsYr”
DoCmd.OpenQuery “qryExcSAFAPN”
DoCmd.OpenQuery “qryExcSAFANbr”
DoCmd.OpenQuery “qryUpdatePNDetails”
DoCmd.OpenQuery “qryUpdatePNDetailsYr”
DoCmd.OpenQuery “qryUpdatePDOCOverview”
DoCmd.OpenQuery “qryUpdateSAFAPN”
DoCmd.OpenQuery “qryUpdateSAFANbr”
DoCmd.SetWarnings True
End Sub

The first half of these queries are make table queries, if that makes a difference. Already tried Echo.

TIA!

Go to the top of the page
 
theDBguy
post May 17 2019, 04:32 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just a guess but if you leave the warnings on and go through all the messages, would it still take 30 minutes to go through all of them? I am also hoping it will give you some indications if any of the queries are running into each other.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post May 17 2019, 04:37 PM
Post#3


UtterAccess VIP
Posts: 1,413
Joined: 4-June 18
From: Somerset, UK


On the same lines as the above, you could try adding the line DoEvents between each query to allow time for processing to complete before moving on to the next query

--------------------
Go to the top of the page
 
DanielPineault
post May 17 2019, 04:46 PM
Post#4


UtterAccess VIP
Posts: 6,635
Joined: 30-June 11



What about using Currentdb.Execute? Just a stab in the dark

CODE
Private Sub CmdUpdateTemp_Click()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database

    Set db = CurrentDb

    If Me.Dirty = True Then Me.Dirty = False
    With db
        .Execute "qryExcPDOCOverview", dbFailOnError
        .Execute "qryExcPNDetails", dbFailOnError
        .Execute "qryExcPNDetailsYr", dbFailOnError
        .Execute "qryExcSAFAPN", dbFailOnError
        .Execute "qryExcSAFANbr", dbFailOnError
        .Execute "qryUpdatePNDetails", dbFailOnError
        .Execute "qryUpdatePNDetailsYr", dbFailOnError
        .Execute "qryUpdatePDOCOverview", dbFailOnError
        .Execute "qryUpdateSAFAPN", dbFailOnError
        .Execute "qryUpdateSAFANbr", dbFailOnError
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CmdUpdateTemp_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub


Could there be some type of locking issue being caused by open forms perhaps?

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th June 2019 - 09:47 AM