Automating Test Data Loading

automating test data loading

Part of the process of creating reports and reviewing the program design involves loading of test data. I wrote a function for Automating Test data loading for the Club Lotto application. The functions loads test data for the complete calendar year.

Using this function I took the ticket prices and added two columns to define if testing should be on a weekly or daily basis. I used a random function to ensure I wouldn’t have the same sales each day or week.

This was coded to loop through the entire calendar year and add sales each day or week generating lotto numbers and adding the content.

To allow the data to be removed from the model I added a new field to the sales table called S_TestEntry and set the value to “Y” to allow the user to easily remove the test entries.

Automating Test Function

On the Configuration form the user has the option of loading or clearing the test data. The code behind each of these buttons is shown below

Automating Test options in configuration screen

Clearing Test Data VBA
Private Sub cmdClearTest_Click()
If MsgBox("Clear Test Data ?", vbYesNo) = vbYes Then
    CurrentDb.Execute "Delete * from tblSales where S_TestEntry='Y'"
    MsgBox "Test Data Erased"
End If

End Sub

Automating Test Data VBA
Function LoadTestData()
'automating test data
Dim rst As Recordset, rstPrices As Recordset, i As Integer, tmpStart As Date, tmpCount, tmpWeekDraw, tmpSalesID, tmpList, tmpSplit
Dim tmpEnd As Date
Set rstPrices = CurrentDb.OpenRecordset("select * from tblPrices")
tmpSalesID = 1
Set rst = CurrentDb.OpenRecordset("tblSales")
DoCmd.Hourglass True
Do While Not rstPrices.EOF
    i = 0
    tmpStart = CDate("1/Jan/" & Year(Date))
    tmpEnd = CDate("31/Dec/" & Year(Date))
    Do While tmpStart < tmpEnd
        If rstPrices!P_TestDaily + rstPrices!P_TestWeekly > 0 Then
            For i = 1 To Round(Int(rstPrices!P_TestDaily + rstPrices!P_TestWeekly) * Rnd) + 1
                rst.AddNew
                rst!S_TestEntry = "Y"
                tmpSalesID = tmpSalesID + 1
                rst!S_DateTime = tmpStart
                rst!S_Email = "2091" & Right("000" & tmpSalesID, 4)
                rst!S_Name = "Test Mode"
                rst!S_TicketTypeText = rstPrices!P_Text
                rst!S_TicketTypeno = rstPrices!P_ID
                rst!S_Lines = rstPrices!P_Tickets
                rst!S_TotalPRice = rstPrices!P_Price
                rst!S_PRice = rstPrices!P_Price
                tmpList = SelectUniqueRandomNumbers
                tmpSplit = Split(tmpList, ",")
                rst!S_N1 = tmpSplit(0)
                rst!S_N2 = tmpSplit(1)
                rst!S_N3 = tmpSplit(2)
                rst!S_N4 = tmpSplit(3)
                rst!S_Exported = -1
                rst!S_ExportedDate = tmpStart
                If rst!S_Lines > 1 Then
                    tmpList = SelectUniqueRandomNumbers
                    tmpSplit = Split(tmpList, ",")
                    rst!S_N5 = tmpSplit(0)
                    rst!S_N6 = tmpSplit(1)
                    rst!S_N7 = tmpSplit(2)
                    rst!S_N8 = tmpSplit(3)
                    tmpList = SelectUniqueRandomNumbers
                    tmpSplit = Split(tmpList, ",")
                    rst!S_N9 = tmpSplit(0)
                    rst!S_N10 = tmpSplit(1)
                    rst!S_N11 = tmpSplit(2)
                    rst!S_N12 = tmpSplit(3)
                End If
                rst.Update
            Next i
        End If
        If rstPrices!P_TestDaily > 0 Then ' daily
            tmpStart = DateAdd("d", 1, tmpStart)
        Else
            tmpStart = DateAdd("ww", 1, tmpStart)
        End If
    Loop
    rstPrices.MoveNext
    
Loop
DoCmd.Hourglass False

MsgBox " Test Data Added"
End Function

Report Samples
Financial Reported generated on Test data

This is used in our Sample Applications. The lotto application is covered in detail in this post.

I met Philipp Stiefel at Access DevCon in Austria a few years ago and he covered this topic in depth. He has multiple videos and content related to this topic Link.

,

Leave a Reply

Your email address will not be published. Required fields are marked *