Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-06-2016, 09:28 AM   #1
fredsmith999
Newly Registered User
 
Join Date: Jul 2016
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
fredsmith999 is on a distinguished road
Looping through recordest very slow

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?

Code:
    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 is offline   Reply With Quote
Old 07-06-2016, 09:36 AM   #2
fredsmith999
Newly Registered User
 
Join Date: Jul 2016
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
fredsmith999 is on a distinguished road
Angry Re: Looping through recordest very slow

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'.

Last edited by fredsmith999; 07-06-2016 at 09:56 AM.
fredsmith999 is offline   Reply With Quote
Reply

Tags
loop , recordset , slow , vba access 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Slow performance (REALLY slow!) shacket General 29 10-06-2011 12:33 PM
Access slow to load [really really slow!] Elfman General 3 12-14-2007 05:32 AM
Reference Subform Recordest From Module Function tembenite Modules & VBA 1 05-17-2005 11:07 AM
looping VB qry? samonwalkabout Queries 32 06-21-2004 06:26 AM
Recordest problem RpbertS Forms 4 03-11-2000 10:41 AM




All times are GMT -8. The time now is 01:00 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World