Update Method

[jeffro]

I'm trying to update the records in my database from vb. I have pretty much havethe code set. There is one problem the data is not changing in the database. Does anyone have any clue what I'm doing wrong?

Here is the code:

Private Sub cmdUpdate_Click()
Dim stroldMachineno As String
Dim stroldMachineDesc As String
Dim stroldDivision As String
Dim strMessage As String

On Error GoTo ErrorUpdate

Set rsMachineInfo = New ADODB.Recordset
rsMachineInfo.Open "Select * From MachineInfo", cnn, adOpenDynamic, adLockOptimistic, adCmdText

stroldMachineno = rsMachineInfo!Machineno
stroldMachineDesc = rsMachineInfo!MachineDesc
stroldDivision = rsMachineInfo!Division

rsMachineInfo!Machineno = txtMachineno
rsMachineInfo!MachineDesc = txtMachineDesc
rsMachineInfo!Division = txtDivision

strMessage = "Edit in progress:" & vbCr & _
" Original data = " & stroldMachineno & " " & _
stroldMachineDesc & "" & stroldDivision & vbCr & " Data in buffer = " & _
rsMachineInfo!Machineno & " " & rsMachineInfo!MachineDesc & "" & rsMachineInfo!Division & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"

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

MsgBox "Data in recordset = " & rsMachineInfo!Machineno & " " & _
rsMachineInfo!MachineDesc & "" & rsMachineInfo!Division

' Restore original data because this is a demonstration.
If Not (stroldMachineno = rsMachineInfo!Machineno And _
stroldMachineDesc = rsMachineInfo!MachineDesc And stroldDivision = rsMachineInfo!Division) Then
rsMachineInfo!Machineno = stroldMachineno
rsMachineInfo!MachineDesc = stroldMachineDesc
rsMachineInfo!Division = stroldDivision
rsMachineInfo.Update
End If

rsMachineInfo.Close

ExitUpdate:
Exit Sub

ErrorUpdate:
MsgBox Err.Number & vbCrLf & Err.Description, , "ERROR"
Resume ExitUpdate
End Sub

You can make changes to a Recordset object that was created using an SQL statement, but since it's not the WHOLE table from the database, it can't modify it.

[jeffro]

So I can not update my table at all?

You could if you would use the whole table.
I'm not a DB expert, so maybe there are methods to bypass this problem.