updating recordset---Best Way

[jadams0173]

I have a recordset based off of a sql query. I think display the rs in an unbound form. If a user changes a value in the form and presses the 'save' button I want to capture that change. What is the best way to do this. I would normally use an update query or put the rs into an array and update the table off of the array.

Is there a way to do it with ADO? I've been reading on batchupdates but I don't know if this is what I want. I don't want to append to the table I just want to update.

 

[Melagan]

What about the Update method? It works with both DAO and ADODB.

This is out of the help file:

'Update Method Example

'This example demonstrates the Update method in conjunction with Edit method.

Sub UpdateX()

   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset
   Dim strOldFirst As String
   Dim strOldLast As String
   Dim strMessage As String

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("Employees")

   With rstEmployees
      .Edit
      ' Store original data.
      strOldFirst = !FirstName
      strOldLast = !LastName
      ' Change data in edit buffer.
      !FirstName = "Linda"
      !LastName = "Kobara"

      ' Show contents of buffer and get user input.
      strMessage = "Edit in progress:" & vbCr & _
         "  Original data = " & strOldFirst & " " & _
         strOldLast & vbCr & "  Data in buffer = " & _
         !FirstName & " " & !LastName & vbCr & vbCr & _
         "Use Update to replace the original data with " & _
         "the buffered data in the Recordset?"

      If MsgBox(strMessage, vbYesNo) = vbYes Then
         .Update
      Else
         .CancelUpdate
      End If

      ' Show the resulting data.
      MsgBox "Data in recordset = " & !FirstName & " " & _
         !LastName

      ' Restore original data because this is a demonstration.
      If Not (strOldFirst = !FirstName And _
            strOldLast = !LastName) Then
         .Edit
         !FirstName = strOldFirst
         !LastName = strOldLast
         .Update
      End If

      .Close
   End With

   dbsNorthwind.Close

End Sub

~Melagan
______
"It's never too late to become what you might have been.

 

[jadams0173]

Thanks for the reply Melagan! I have used the .Update method before. The problem that I was looking for a better way around is, I have to know what changed and to what record the change was made.

For example if the below were displayed in an unbound form and the user made changes, how could I only update the records that were changed?

Original
Data1 Data2
a 1
b 2
c 3
d 4

Updates
Data1 Data2
a 2
b 1
c 3
d 4

I'm trying to see if there is a better way then the way's I've been doing it.

 

[Melagan]

What kind of timeframe are we looking at for the "history" of changes here. Do you want to keep the original data in a variable to be used in code later, or do you need to store this data indefinitely? If the latter, you could consider making a table to store said data and use an append query to append the current record to the history table before the data is changed... just an idea. I use the same idea when users delete data - when they hit my "delete" command buttons, they're actually doing two things - appending the data to a "deleted items" table then deleteing the data from the "real" table.

~Melagan
______
"It's never too late to become what you might have been.

 

[Remou]

Depending on your answer to Melagen's post, GetRows, may be useful.

 

[jadams0173]

Melagan,
The original data does not need to be stored for any length of time. As soon as the user makes changes and presses the save button the old data is gone and overwritten with the new data. No archive or history table is needed.

I like your idea of a delete table. Currently I just put a checkbox in the table that when checked it means it is "deleted". This way it is ignored by all queries and other searcheds. The only down side is the table tends to grow rather quickly as old tools are removed and new ones are added. I may look into doing something like that.

Remou,
I've only used GetRows as an alternative to get the recordcount from tables. I will read more about it. Please share your thoughts on how this may be helpful.

 

[Remou]

Very roughly:

Option Compare Database
Dim ars As Variant

Private Sub cmdSave_Click()
Set rs = Me.Members subform].Form.RecordsetClone

'Loop through array and recordset to find mismatches
For i = 0 To UBound(ars, 2)
    'Text type id
    rs.FindFirst "[Code]='" & ars(0, i) & "'"
    For j = 1 To UBound(ars, 1)
        If ars(j, i) <> rs.Fields(j) Then
            Debug.Print "Code " & ars(0, i) & ": " & rs.Fields(j); " was " & ars(j, i)
        End If
    Next
Next

End Sub

Private Sub Form_Load()
Dim rs As DAO.Recordset

strSQL = "SELECT [Members].[Code], " _
       & "[Members].[Identifier], " _
       & "[Members].[Institution] " _
       & "FROM Members;"
       
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.EOF Then
    rs.MoveLast
    rs.MoveFirst
    intRows = rs.RecordCount
    ars = rs.GetRows(intRows)
Else
    'No data
End If
End Sub

 

[PHV]

display the rs in an unbound form
Could you please post the code doing that (including the rs instantiation) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

Sub EditPartNumber()
Dim sql         As String
Dim c           As Integer
Dim strDesc         As String
Dim strTime         As String


strDesc = "txtStepDesc"
strTime = "txtTime"

Set conn = CurrentProject.Connection
Set g_rsEdit = New ADODB.Recordset

sql = "SELECT Customerid, PN, DESCRIPTION, STEP1DESCRIPTION, STEP1TIME, id " _
    & "FROM tblProcessSteps " _
    & "WHERE PN='" & cboPN & "'"

With g_rsEdit
    .CursorLocation = adUseClient
    .Open sql, conn, adOpenForwardOnly, adLockBatchOptimistic, adCmdText
    
    If .EOF Then
        Set g_rsEdit = Nothing
        Set conn = Nothing
        Call ClearForm_partial(Me.Form)
        Exist = False
        Exit Sub
    End If

'this disconnects the recordset
 'Set .ActiveConnection = Nothing
 'conn.Close
 
 'g_RC = .RecordCount
'DISPAY THE RECORDS FOR DESCRIPTION AND TIME IF THEY EXIST
For c = 1 To .RecordCount
    Me(strDesc & c) = .Fields("step1description")
    Me(strTime & c) = .Fields("step1time")
    .MoveNext
Next c
Exist = True
End With

    
End Sub

 

[JerryKlmns]

jadams0173,

Inside your With .. End With bloc

Only a setting of adOpenStatic is supported if the CursorLocation property is set to adUseClient. If an unsupported value is set, then no error will result; the closest supported CursorType will be used instead.

Although Supports(adUpdateBatch) may be true for dynamic and forward-only cursors, for batch updates you should use either a keyset or static cursor. Set the LockType property to adLockBatchOptimistic and the CursorLocation property to adUseClient to enable the Cursor Service for OLE DB, which is required for batch updates.

Plus

    If .EOF Then
        [b] .Close [/b]
        Set g_rsEdit = Nothing
        [b] conn.Close [/b]
        Set conn = Nothing
        Call ClearForm_partial(Me.Form)
        Exist = False
        Exit Sub
    End If