Updating a table?

[jeffro]

Hey

I'm trying to update a database table from vb. When I make a change to 2 out of the 3 fields and click on cmdupdate I get an syntax error (missing operator)in query expression '*Machineno'. I'm not sure what it is telling me. Have any ideas?

Here is the code from the cmdupdate:

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 * Machineno, MachineDesc, Division 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

I think if U want all columns selected youre string must be like
"Select * From MyTable"
If You only want some columns it must be like
"Select Field1, Field2, Field3 From MyTable"
As I can see you ask "Select *Field , field1 From MyTable"
The asterisk is there too much

[jeffro]

Thanks for trying to help, when I take the asterik out of the select statement it goes through the steps of the update. But it does not actually update.

jeffro,

you are actually overwriting your own update. maybe this will help:

'**********************************************************

Private cnn As New ADODB.Connection
Private rsMachineInfo As ADODB.Recordset

Private Sub Command1_Click()

Dim stroldMachineno As String
Dim stroldMachineDesc As String
Dim stroldDivision As String
Dim strMessage As String
Dim strSql As String
Dim fldNo As Field
Dim fldDesc As Field
Dim fldDiv As Field

strSql = "Select Machineno, MachineDesc, Division From MachineInfo"

Set rsMachineInfo = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=c:\db1.mdb"

With rsMachineInfo
.Open strSql, cnn, adOpenDynamic, adLockOptimistic

Set fldNo = .Fields("Machineno")
Set fldDesc = .Fields("MachineDesc")
Set fldDiv = .Fields("Division")

stroldMachineno = fldNo
stroldMachineDesc = fldDesc
stroldDivision = fldDiv

fldNo = txtMachineno
fldDesc = txtMachineDesc
fldDiv = txtDivision

strMessage = "Edit in progress:" & vbCr & _
" Original data = " & _
stroldMachineno & " " & stroldMachineDesc & "" & stroldDivision & vbCr & _
" Data in buffer = " & _
fldNo & " " & fldDesc & "" & fldDiv & 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

MsgBox "Data in recordset = " & fldNo & " " & fldDesc & "" & fldDiv

' If Not (stroldMachineno = fldNo And stroldMachineDesc = fldDesc And stroldDivision = fldDiv) Then
' fldNo = stroldMachineno
' fldDesc = stroldMachineDesc
' fldDiv = stroldDivision
' .Update
' End If

.Close
End With

cnn.Close

End Sub

'**********************************************************

a few other suggestions:
- you open the entire table to update only one record
- don't use adOpenDynamic, if possible
- why do you first write data in the recordset and then ask the user?

hope that helps,

Sascha

[jeffro]

Thanks Sascha

That helped out a lot, I thought I was going to go crazy trying to make this update work. Also thank you for the extra pointers, I will definitley keep them in mind!