How to do an APPEND QRY

I have a table that I had a field "archive" (YES/NO) and I want to update it
depending the value of another field "Tstat" in the same table.

I did it with VISUAL BASIC codes as follow :

Dim DB1 As ADODB.Connection
Set DB1 = CurrentProject.Connection

Dim RS1 As New ADODB.Recordset

RS1.Open "Board_Data", DB1, adOpenStatic, adLockPessimistic
RS1.MoveFirst
Do Until RS1.EOF
If RS1!Tstat = 14 Then
RS1!archive = true
End If
RS1.MoveNext
Loop
RS1.Close
End Sub

Problem is I have an error message "maxlocksperfile" to increase...

Is there an easiest way to do this with an APPEND or UPDATE QRY ?

It's almost always more efficient to use SQL, rather than looping through a
recordset.

All you need is "UPDATE Board_Data SET Archive = True WHERE Tstat = 14"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ben" wrote in message ...
I have a table that I had a field "archive" (YES/NO) and I want to update
it depending the value of another field "Tstat" in the same table.

I did it with VISUAL BASIC codes as follow :

Dim DB1 As ADODB.Connection
Set DB1 = CurrentProject.Connection

Dim RS1 As New ADODB.Recordset

RS1.Open "Board_Data", DB1, adOpenStatic, adLockPessimistic
RS1.MoveFirst
Do Until RS1.EOF
If RS1!Tstat = 14 Then
RS1!archive = true
End If
RS1.MoveNext
Loop
RS1.Close
End Sub

Problem is I have an error message "maxlocksperfile" to increase...

Is there an easiest way to do this with an APPEND or UPDATE QRY ?