Updating the table using recordset

vb6

I have a recordset rcdDNE. I want to update my reclamation by making some conditions with my existing recordset. But my table is not updating. Can you guys tell me where I am 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

Best Answer

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.

Related Solutions

Sql – find the duplicate and write it in log file

More of a strategy then a specific answer but ...

When importing data from external sources we'll often insert the data into staging tables that do not have the same keys/contraints placed on them and then sanitize the data prior to insertion.

What is done during "sanitation" depends on your requirements (for example, when you have two of the same account numbers are the records the same or are the data fields different, if the fields are different, how do you choose which data to use?). And then insert/move it into the production table once sanitization is complete.

Assign table values to recordset and update with another recordset using vb6

An ADODB recordset is not a mirror of a database table. The recordset contains whatever you want it to based on the query you provide it. So to load the data from the database into the recordset you need to execute a query. This can be done in two ways.

Use the ADODB.Connection.Execute method and set your recordset to the result.

Dim con as New ADODB.Connection  
Dim rs as ADODB.Recordset  
con.ConnectionString = "some connection string"  
con.Open  
Set rs = con.Execute("SELECT * FROM table")

Create an ADODB.Recordset object, specify the connection and then call the Open method passing it a query.

Dim con as New ADODB.Connection  
Dim rs as New ADODB.Recordset  
con.ConnectionString = "some connection string"  
con.Open  
Set rs.ActiveConnection = con  
rs.Open "SELECT * FROM table"

The query can be as simple or complex as you want it be. The query could ask for fields from multiple tables and the recordset will still contain the results of the query, however you won't be able to tell which table the fields are from.

Best Answer

Related Solutions

Sql – find the duplicate and write it in log file

Assign table values to recordset and update with another recordset using vb6

Related Topic