How to speed up a table initialization loop?

[rwwilliamson]

Upon a user request, the following code gets executed to clean out and initialize the "Scores" table with a competitor number and an event number. Each competitor can have 20 event scores. A crosstab query is used to gather up all the appropriate scores for each competitor and calculate a final total score. The below code runs correctly, but the speed is abysmally slow and it takes 30+ seconds to initialize the 4000 records (200 competitors x 20 events). Is there a faster way to perform this simple action?

Function InitScores()
Dim dbsCurrent As Database
Dim EventNum As Integer, Competitor As Integer, i As Integer, j As Integer
Dim Scores As String

Set dbsCurrent = CurrentDb
Call dbsCurrent.Execute("delete *.* from Scores", dbFailOnError)

For i = 1 To 200 Step 1
For j = 1 To 20 Step 1
Call dbsCurrent.Execute("insert into Scores (Competitor, EventNum) values (" & i & ", " & j & " )")
Next
Next

End Function

 

[pbaldy]

I suspect opening a recordset first then using the AddNew method within the loop would be faster.

 

[gemma-the-husky]

why delete and recreate at all?

you have a table with 4000 lines

why not just update everything to value 0 - this would be almost instantaneous

if 0 is a possible score, then update everything to -1 (or have a flag to hold this info)

then in your queries, ignore anything thats not set.

no problem, as long as you have sufficient space for all the competitors. (ie no more than 200)

 

[rwwilliamson]

Thanks, Dave, that's an elegant solution. And fast as you said. If the "Scores" table never gets damaged, then just doing an update makes the most sense. I can keep the existing code with an option to rebuild the table if more competitors are added or events are added. I got off track because there are numerous fields within a record, some of which are checkboxes but most are numeric fields of float/integer and a memo field to collect any notes from the official about the score if there is a penalty assigned. It just never occurred to me to do a simple update to the variable fields and leave the competitor and event fields untouched. I love this forum.

Best regards,
ron

 

[Atomic Shrimp]

It ought to be possible to create a crosstab based on competitors and events tables, even if the scores table is completely empty - that way, you wouldn't have to prepare an table full of empty records ready to receive the scores, just zap the table, then append into the scores table as scores roll in.