Looping through recordest very slow

[fredsmith999]

I needed to create a crosstab style report with varying columns and conditional formatting. I know there are some workarounds to do this in Access, but after a lot of frustration I ended up exporting the data to a html file instead, where some simple javascript displayed it and formatted it.

In fact, since I am quite inexperienced with VBA (and dislike VBA), my VBA code just dumps it to the html file as JSON. This mean that the javascript has to reinvent the wheel somewhat in order to display it, but it is only a couple of dozen lines and it runs instantaneously.

Unfortunately, my VBA is extremely slow. My SQL query returns 2400 records and then the loop itself takes about 15 seconds. I wonder if it is the many field lookups, or even my continually concatenating onto the end of the output string. Any ideas?

    Dim sqlString, output, score, studentFirstName, studentSurname, testName, testOutOf, testSecuringThreshold, testMasteringThreshold, testDate, testID, studentID, testSubject, absent As String
    Dim rsStudents As DAO.Recordset
    sqlString = "SELECT students.*, tests.*, test_results.*, subjects.* FROM subjects INNER JOIN (tests INNER JOIN (students INNER JOIN test_results ON students.ID = test_results.student) ON tests.ID = test_results.test) ON subjects.ID = tests.subject WHERE (" & strFilter & ") ORDER BY tests.test_date"
    Set rsStudents = CurrentDb.OpenRecordset(sqlString)
    If Not (rsStudents.EOF And rsStudents.BOF) Then
        rsStudents.MoveFirst
        Do Until rsStudents.EOF = True
            studentFirstName = rsStudents!student_first_name
            studentSurname = rsStudents!student_surname
            studentID = rsStudents.Fields("students.ID")
            score = rsStudents!score
            testName = Replace(rsStudents!test_name, """", """)
            testOutOf = rsStudents!out_of
            testSecuringThreshold = rsStudents!securing_threshold
            testMasteringThreshold = rsStudents!mastering_threshold
            testDate = rsStudents!test_date
            testSubject = rsStudents.Fields("subjects.subject")
            testID = rsStudents.Fields("tests.ID")
            absent = rsStudents!absent
            
            output = output & vbCrLf & "{""studentFirstName"": " & """" & studentFirstName & """, " & """studentSurname"": " & """" & studentSurname & """, " & """studentID"": " & """" & studentID & """, " & """testID"": " & """" & testID & """, " & """testDate"": " & """" & testDate & """, " & """testName"": " & """" & testName & """, " & """testSubject"": " & """" & testSubject & """, " & """score"": " & """" & score & """, " & """absent"": " & """" & absent & """, " & """testOutOf"": " & """" & testOutOf & """, " & """testSecuringThreshold"": " & """" & testSecuringThreshold & """, " & """testMasteringThreshold"": " & """" & testMasteringThreshold & """}, "
            
            rsStudents.MoveNext
        Loop
    End If

 

[fredsmith999]

May have answered my own question...

excelramblings.blogspot .co.uk/2013/03/strings-and-garbage-collector-in-vba.html

msdn.microsoft .com/en-us/library/ms182272(v=vs.80).aspx
(Do not concatenate strings inside loops)

(Added a space inside each url otherwise wouldn't let me post)

EDIT: Indeed. I changed the output variable to an array, and used 'Join' on it at the end. It is now fast. But it reminded me how barbaric VBA is. Nothing wrong with garbage collection problems, they affect the most fashionable languages too. But to find the length of the recordset, I had to 'MoveLast' and to set the array length to that, I had to 'ReDim'.