VBA Access - Having trouble inserting multiple records into a table

Question

I am new to VBA, so I'm having trouble understanding what is going on. I am trying to fill a table with 50,000 records with the following code:

Sub arrayData()
Dim custnames() As Variant
Dim num As Long, dbs As Database, InsertRecord As String
Dim CusSalaryId As Long, num1 As Long, EmpId As Long
Dim EmpSalary As String
Set dbs = CurrentDb()
EmpId = 0
CustSalaryId = 0
For num1 = 0 To 50000
CustSalaryId = CustSalaryId + 1
EmpId = EmpId + 1
custnames = Array("$1000", "$500", "$300", "$600")



num = Int((UBound(custnames) - LBound(custnames) + 1) * Rnd + LBound(custnames))
EmpSalary= custnames(num)

InsertRecord = "insert into SALARY (SalaryID, NetSalary, EmployeeID) 
values (" & "'" & CustSalaryId & "'" & "," & "'" & EmpSalary & "'" & "," & "'" & EmpId & "'" & ")"

dbs.Execute InsertRecord
Debug.Print SalaryId; EmpSalary; EmpId;

Next

End Sub

When I run it, I get only 2 records: Salary Table Output

I don't see any problem in your code! I suspect you might have any constraint in your table. Can try to see this MsgBox (num1) after the For loop? — Md. Suman Kabir
No, please put it after Next so that we can see the last value only. — Md. Suman Kabir
Then i can say your code is correct! Did you check your table now? — Md. Suman Kabir
whats the debug.print output for "Debug.Print SalaryId; EmpSalary; EmpId;" ? does it display all 50000 lines? — FastJack

Unknown Unknown · Accepted Answer · 2018-09-12T07:02:24

I have figured it out, sorry for wasting your time! It was because of the relationships I've created as salary table was linked to the employee table which only had 2 records therefore salary table was only able to create a maximum of 2 records. I am sorry again for not double checking everything before posting.

VBA Access - Having trouble inserting multiple records into a table

1 Answers