CODEWITHSUNDEEP
performancems-accessvba
Emil Olsen
Emil Olsen

Reputation: 352

Access VBA slow after first run

I have a general question. I think it is not code related. I have a small access program using forms, SQL and VBA.

The VBA mainly calculate pretty simple things, but with a lot of data and some SQL runs. I have a status bar where i can see "percentage done". I start the script by clearing all tables and after that running all queries to make sure they're empty. I then run through the data. It works good. It takes around 2 mins.

I then do it again. But now it takes 10 mins. For the same procedure.

If i restart access, it takes 2 mins the first time, then 10 mins afterwards.

When i restart access, the tables are still filled. So it is not because they are empty.

Is there a command to clear all memory or whatever might be needed?

Any suggestions?

The code that runs 10x slower:

For counter = 1 To n_bins
    Application.Echo False

    DoCmd.OpenQuery "q_PowerBinned"
    If DCount("*", "q_PowerBinned") = 0 Then
        DoCmd.OpenQuery "q_000"
        DoCmd.RunSQL "DELETE * FROM q_000"
        DoCmd.Close
        strTMP = (counter - 1) * [Forms]![f_main]![PowerBinCombo] & " - " & counter * [Forms]![f_main]![PowerBinCombo] & " kW"
        strSQL = "INSERT INTO q_000 (Bin, Zero1, Zero2, Zero3, Zero4, Zero5) VALUES ('" & strTMP & "','0','0','0','0','0');"
        DoCmd.RunSQL strSQL
        DoCmd.OpenQuery "q_Move000"
        DoCmd.Close
    Else
        DoCmd.Close
    End If

    DoCmd.OpenQuery "q_Average_Temp"
    DoCmd.Close
    DoCmd.OpenQuery "q_MoveAverage"

    DoCmd.OpenQuery "q_PowerBinned_VG"
    If DCount("*", "q_PowerBinned_VG") = 0 Then
        DoCmd.OpenQuery "q_000_VG"
        DoCmd.RunSQL "DELETE * FROM q_000_VG"
        DoCmd.Close
        strTMP = (counter - 1) * [Forms]![f_main]![PowerBinCombo] & " - " & counter * [Forms]![f_main]![PowerBinCombo] & " kW"
        strSQL = "INSERT INTO q_000_VG (Bin, Zero1, Zero2, Zero3, Zero4, Zero5) VALUES ('" & strTMP & "','0','0','0','0','0');"
        DoCmd.RunSQL strSQL
        DoCmd.OpenQuery "q_Move000_VG"
        DoCmd.Close
    Else
        DoCmd.Close
    End If

    DoCmd.OpenQuery "q_Average_Temp_VG"
    DoCmd.Close
    DoCmd.OpenQuery "q_MoveAverage_VG"
    Application.Echo True

    ' Theoretical of Measured Power Curve

    Percentage = ((counter) / (n_bins)) * 100
    strStatus = "Binned " & Percentage & " %"

    Call dsp_progress_AfterUpdate
    Me.Refresh
    dsp_progress.SetFocus
    dsp_progress.SelStart = 0
    dsp_progress.SelLength = 0
    DoEvents
Next counter

Upvotes: 0

Views: 442

Answers (1)

Dane I
Dane I

Reputation: 742

This happens for one of my access databases. I find that if I do a compact & repair after the appropriate tables have been emptied, the vba run-time returns to the short time again. Not the most elegant of solutions I must admit.

Upvotes: 1

Related Questions