-
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.
-
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.