Query to update N records

[RossWindows]

I've been out of the loop for a while so I can't remember the best course of action here.

I have a table with a list of Orders that need to be worked by a team in our company. I also have a form that Administrators use to select a batch of those orders to work. The options they select are: Number of orders they want to work and what time zone they want the orders to be in. I have the following query which is compiled in the form's code, based on the Administrator's selected options...

        strSQL = "SELECT TOP " & intRequest & " tblAvailableOrders.OrderNumber, 
                tblAvailableOrders.TimeZoneDesc, tblAvailableOrders.intCallCenterID 
                FROM tblAvailableOrders"
 
        If cboTimeZone <> "ALL" Then
            strSQL = strSQL & " WHERE (((tblAvailableOrders.TimeZoneDesc)='" & cboTimeZone & "'));"
        Else
            strSQL = strSQL & ";"
        End If

So, my question now is; how do I go about traversing through each OrderNumber in the query results and UPDATE the corresponding intCallCenterID in tblAvailableOrders with, i.e., a number 1?

I would like to have this all done within VBA if possible.

 

[pbaldy]

You can open a recordset on strSQL and loop through the records, using the Edit method of the recordset.

 

[RossWindows]

Thanks for your suggestion, it definitely got me back in step.
Here's what I have so far:

        strSQLSelect = "SELECT TOP " & intRequest & "tblAvailableOrders.OrderNumber, tblAvailableOrders.TimeZoneDesc, tblAvailableOrders.intCallCenterID FROM tblAvailableOrders WHERE (((tblAvailableOrders.intCallCenterID) Is Null))"
 
        If cboTimeZone <> "ALL" Then
            strSQLSelect = strSQLSelect & " AND (((tblAvailableOrders.TimeZoneDesc)='" & cboTimeZone & "'));"
        Else
            strSQLSelect = strSQLSelect & ";"
        End If
 
        intCallCenter = Me.Parent!frmCallCenter.Value
 
        Set rs = CurrentDb.OpenRecordset(strSQLSelect)
 
        With rs
            If Not .EOF Then
                .MoveFirst
                Do Until .EOF
                    tmpOrder = !OrderNumber
                    CurrentDb.Execute "UPDATE tblAvailableOrders SET tblAvailableOrders.intCallCenterID = " & intCallCenter & _
                        " WHERE (((tblAvailableOrders.OrderNumber)='" & tmpOrder & "'));", dbFailOnError
                    .MoveNext
                Loop
            End If
        End With

This is working fine, but when there are more than a couple hundred orders, the UPDATE loop is quite slow. Is there a more elegant/efficient way that you know of?

 

[pbaldy]

Like I said, I would use the Edit method of the recordset. You already have the record there, so no reason to build/execute SQL. It should be noticeably faster than the SQL.

.Edit
!intCallCenterID = intCallCenter
.Update

The fastest would be a single update query, but off the top of my head I don't know if you can use TOP with it.

 

[RossWindows]

Oh okay... for some reason the original SELECT query was not updateable so I felt the need to use a separate UPDATE query.
It's updateable now so I tried the .Edit method again but it is still painfully slow. At this point, I should mention that the backend is in SQl Server 2008, but I NEVER have this kind of lag unless something is written inefficiently in the front-end to begin with.

 

[bparkinson]

Have you considered creating a parameterized stored procedure? That's how I work with SQL Server from Access, and a set-based solution is much faster than looping through a recordset. If you can do that (I understand that sometimes the SQL Servers are controlled by DBA's that won't let you create SP's, I am one of those DBA's) and want to, I think you can solve the performance problems.