Hey everyone, first time posting here. I'm a baseball statistical analyst and I created a team simulation in Excel that can run through an entire season a specified number of times, reporting the W-L for each team in each instance. I want to automatically upload the data into an Access database. For instance, I'd like to run through the season 20,000 times in Excel and my VB code currently reports all results into a worksheet called Results, so it would have 600,001 rows in that file (one extra with headings).

I created an Access DB titled Sim_Results.mdb, with the table Sim_Test that houses fields Sim, Team, Wins, Losses. I'm having trouble with this code, however, and am looking for assistance.

Sub DAOFromExcelToAccess()
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Eric\Desktop\Simulations\Sim_Results.mdb")
Set rs = db.OpenRecordset("Sim_Test", dbOpenTable)
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew
.Fields("Sim") = Range("A" & r).Value
.Fields("Team") = Range("B" & r).Value
.Fields("Wins") = Range("C" & r).Value
.Fields("Results") = Range("D" & r).Value
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

When I run that I get an error box with the Red X but no description of the error.

How can I get around this? Basically, I run the macro in the simulation which reports the 20,000 run-throughs into the Results worksheet tab.... then I have a button on that Results tab with the macro above, so it would be a two-step process, but I can't get the above macro to work.