Batch Update

[Tyork]

I've created a task scheduler and based on certain conditions tasks will be scheduled into a database. Below is a sample which basically adds a task occurrence every weekday for 2500 iterations which turns out to be approx 7 years.

 
    Case 3

        For x = 0 To 2499
        
        Day = sd + x
        
        DayOfWeek = Weekday(Day)
                        
            If DayOfWeek = 2 Or DayOfWeek = 3 Or DayOfWeek = 4 Or DayOfWeek = 5 Or DayOfWeek = 6 Then
            
            SQLAddOccurrence3 = "INSERT INTO [BMS_OCCURRENCE] (TaskID,TaskName, Day, Owner, Deadline1, Deadline2, Deadline3, Deadline4)" & _
                "VALUES('" & TaskID & "', '" & TN & "', '" & Day & "', '" & OW & "', '" & d1 & "', '" & d2 & "', '" & d3 & "', '" & d4 & "');"
            
            DoCmd.SetWarnings False
            
                DoCmd.RunSQL SQLAddOccurrence3
            
            DoCmd.SetWarnings True
        
            End If
        
        Next x

The code executes properly. The issue is that when I run this locally it executes within 4 seconds. It takes 75 seconds to run this on the network. I had the DBA in my office and he did not provide me any solutions other than to update it as a batch instead of writing to the table at the end of each loop. I'm not sure how to go about a batch update; would anybody be able to help me out? I would appreciate any suggestions or help. Thanks!

[pbaldy]

This is not a direct answer to your question, but in my experience a recordset would be much faster. This type of thing, which can be adapted (rsLocal was previously opened on the target table):

    With rsLocal
      .AddNew
      !CustomerKey = rst!Cust_Key
      !CustomerName = rst!Name
      .Update
    End With

That said, I'd offer another thought. Do you really want to add 7 years worth? You know your data better than I do of course, but I have situations where I insert standing reservation data for certain customers. What I do is create an automated process that will insert reservations 2 months out on the first of the month. In other words, on Dec 1 the reservations for Feb will get added. That type of thing wouldn't be hard to set up to insert them a week, month, or whatever ahead.

[KathyL]

I've created a task scheduler and based on certain conditions tasks will be scheduled into a database. Below is a sample which basically adds a task occurrence every weekday for 2500 iterations which turns out to be approx 7 years.

The code executes properly. The issue is that when I run this locally it executes within 4 seconds. It takes 75 seconds to run this on the network. I had the DBA in my office and he did not provide me any solutions other than to update it as a batch instead of writing to the table at the end of each loop. I'm not sure how to go about a batch update; would anybody be able to help me out? I would appreciate any suggestions or help. Thanks!

In no circumstance should you expect the code to operate at the same speed on a network as it can operate on a stand alone local PC.

I don't think updating it as a batch would change a thing.

I also think 75 seconds to be very reasonable. I've got action queries that do something similar off a network and get slower responses than you, and I have to accept it for what it is.