چگونه برای تست برنامه های خود دیتا بسازیم:
Option Compare Database
Option Explicit
Public Function RandomX(Min As Long, Max As Long) As Long
RandomX = Int((Max - Min + 1) * Rnd + Min)
End Function
Public Function FillData()
CurrentDb.Execute "CREATE TABLE Persons (PersonID COUNTER PRIMARY KEY , Person CHAR(20))"
CurrentDb.Execute "CREATE TABLE Payments (PaymentID COUNTER PRIMARY KEY , PersonID LONG , Amount LONG)"
CurrentDb.Execute "CREATE TABLE Orders (OrderID COUNTER PRIMARY KEY , PersonID LONG , Amount LONG)"
Dim i, j As Integer
For i = 1 To 20
CurrentDb.Execute ("INSERT INTO PERSONS (Person) VALUES('PERSON " + Format(i, "000") + "')")
Next
Dim RS As Recordset
Set RS = CurrentDb.OpenRecordset("PERSONS")
Dim PID As Long
Do While Not RS.EOF
PID = RS("PersonID")
j = RandomX(0, 6)
For i = 1 To j
CurrentDb.Execute ("INSERT INTO Payments (PersonID,Amount) VALUES(" & PID & "," & RandomX(1000, 100000) & ")")
Next
j = RandomX(0, 5)
For i = 1 To j
CurrentDb.Execute ("INSERT INTO Orders (PersonID,Amount) VALUES(" & PID & "," & RandomX(1000, 100000) & ")")
Next
RS.MoveNext
Loop
Set RS = CurrentDb.OpenRecordset("SELECT PersonID, Balance FROM Totals WHERE PersonID In (3,7,12,15,18)")
Do While Not RS.EOF
If RS("Balance") > 0 Then
CurrentDb.Execute ("INSERT INTO Orders (PersonID,Amount) VALUES(" & RS("PersonID") & "," & RS("Balance") & ")")
ElseIf RS("Balance") < 0 Then
CurrentDb.Execute ("INSERT INTO Payments (PersonID,Amount) VALUES(" & RS("PersonID") & "," & (-RS("Balance")) & ")")
End If
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
End Function