updating the table using recordset.

Question

Hi I have a recordset rcdDNE. I want to update my reclamation by making some condition with my existing recordset. But my table is not updating. Can you guys tell me where I ma doing wrong.

    Dim lngRecCount As Long

frmDNELoad.lblStatus.Caption = "Updating records in Reclamation Table..."
frmDNELoad.Refresh
CqDate = Format(Date, "dd/MM/yyyy")

Set rcdreclamation = New ADODB.Recordset
With rcdreclamation
    .ActiveConnection = objConn
    .Source = "SELECT * FROM T_DATA_reclamation"
    .CursorType = adOpenDynamic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open
End With

rcdDNE.MoveFirst

Do Until rcdDNE.EOF
With cmdDNEFRC
    .ActiveConnection = objConn
    .CommandText = "update t_data_reclamation set ClaimStatus = 'C',DateClosed = 'CqDate', Audit_LastUpdated = 'CqDate', Audit_UserAdded = 'SYSTEM' where RTProvided = '" & rcdDNE("AccountNbr") & "'"
    .CommandType = adCmdText
End With

rcdDNE.MoveNext
Loop

Answer 1

Unless its something you forgot to put in your sample code, You are missing a call to the Execute function inside your Command object's with block.

With cmdDNEFRC
  .ActiveConnection = objConn
  .CommandText = "update t_data_reclamation set ClaimStatus = 'C',DateClosed = 'CqDate', Audit_LastUpdated = 'CqDate', Audit_UserAdded = 'SYSTEM' where RTProvided = '" & rcdDNE("AccountNbr") & "'"
  .CommandType = adCmdText
  .Execute 'dont forget execution 
End With

Also when writing data to the table, using Connection objects BeginTrans and CommitTrans function is recommended, just in case something has to go wrong when writing the data you don't end up with data inconsistencies.