Code run speed on SharePoint linked list (1 Viewer)

jwillet1 · Feb 3, 2014

I have this code that runs on my SharePoint linked database that is used to calculate the length of certain tests. The issue is that the code takes a decent amount of time to run and whenever it runs there is a time in which the interface is unusable while the code runs. I have it set to run "on load" of a form, which is set to open when the database opens, the main switchboard. However whenever the user returns to this main screen the code runs and the database freezes until the code finishes. My question, is there anyway to have to code only run once per session or some way to make the code better and run faster?

Code is attached below.

Code:

Public Function FrequencyCalc()

Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim m_diff As Double

On Error GoTo FrequencyCalc_Error

Set db = CurrentDb
Set rst1 = db.OpenRecordset("qryTestDataDaysPerPhaseStatus", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qryTestDataDaysPerPhaseStatus", dbOpenDynaset)
rst2.MoveNext

Do While Not rst1.EOF
    If rst1!vehicleNumber = rst2!vehicleNumber Then
        If Not rst2.EOF Then
            m_diff = rst2!testDateTime - rst1!testDateTime
            m_diff = Round(m_diff, 2)
            If rst1!TestStatus = "Phase Complete" Or rst1!TestPurpose = "Vehicle Release" Then
                m_diff = 0
            End If
            With rst1
                .Edit
                !Days = m_diff
                .Update
                rst2.MoveNext
                .MoveNext
            End With
        End If
    Else:
        m_diff = Now() - rst1!testDateTime
        m_diff = Round(m_diff, 2)
        If rst1!TestStatus = "Phase Complete" Or rst1!TestPurpose = "Vehicle Release" Then
                m_diff = 0
        End If
        With rst1
            .Edit
            !Days = m_diff
            .Update
            rst2.MoveNext
            .MoveNext
        End With
    End If
    If rst2.EOF Then
        m_diff = Now() - rst1!testDateTime
        m_diff = Round(m_diff, 2)
        If rst1!TestStatus = "Phase Complete" Or rst1!TestPurpose = "Vehicle Release" Then
                m_diff = 0
        End If
        With rst1
            .Edit
            !Days = m_diff
            .Update
            .MoveNext
        End With
        Exit Do
    End If
Loop
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
db.Close
Set db = Nothing

FrequencyCalc_Exit:
Exit Function

FrequencyCalc_Error:
MsgBox Err & " : " & Err.Description, , "FrequencyCalc()"
Resume FrequencyCalc_Exit
End Function

Mihail · Feb 3, 2014

In order to not freeze the interface insert DoEvents statement in the most internal cycle.
Note that this is not a solution. It is something that allow you to use the interface while the code is running.

I don't fully understand what you are trying to do but seems that you wish to update the table with a calculated value. This is wrong. You should never store in a table what you can calculate.
Then, seems that your code can be replaced with one or two queries that, for sure, will run much faster.

jwillet1 · Feb 3, 2014

Attached is a picture of the table that stores the information that I am using the code to calculate values for. As you can see there are a number of vehicles that we do tests on as well as a number of statuses each vehicle test phase goes through. The query that is pulled for the code to work on is set up so that it sorts all of the test data first by vehicle then by date. This sorts the data in to a time line of the vehicles test history and allows for accurate calculation of the time take for each step of the test process. The code then calculates the difference between the first record and second record unless the vehicleID changes, then it calculates from the last recorded time for a vehicle to Now().

There are some checks for certain statuses but that is the gist. I had looked all over for a way to do this before I settled on this code. If there is a better way to do it by all means I am more than happy to do so, however I couldn't find any other way to do exactly what I needed.

Mihail · Feb 3, 2014

For sure it is a better (and simpler) way.
Sanitize your DB in order to not contain confidential information, Save As 2003 version and upload it.
And, enumerate the requests using as few words you can.

PS.
I think that is enough to upload only the table that I see in the pic.
Transform your query in a Make Table query, run it then import the table in a new, 2003, DB.
Upload it.

Warning ! Work on a copy of your DB.

jwillet1 · Feb 3, 2014

I have uploaded a copy of the DB, with only the pertinent tables and the query in question that I am using in the code.

Basically I need how long each test status takes for each vehicle, and if the "teststatus" or "testPhase" is phase complete or vehicle release, respectively, the value should be zero.

Mihail · Feb 4, 2014

Hope this will speed up your query.

I created a temporary table but you can use also the Create Table query (of course, should transform it in a Select query) instead the temporary table, but I think that this approach is not as faster.

jwillet1 · Feb 4, 2014

Mihail --

Thank you so much for your help. I implemented your solution in to my dev database and everything works great, no slow downs and everything still works. The solution you came up with was great. I would never have thought to use a dmin lookup like that, but it works just right.

Mihail · Feb 5, 2014

Glad to help you.
Good luck !

Code run speed on SharePoint linked list (1 Viewer)

jwillet1

Registered User.

Mihail

Registered User.

jwillet1

Registered User.

Attachments

Mihail

Registered User.

jwillet1

Registered User.

Attachments

Mihail

Registered User.

Attachments

jwillet1

Registered User.

Mihail

Registered User.

Similar threads

Users who are viewing this thread