can anyone see an error in this rs.update code?

'* Open a new connection
Set conn2 = New ADODB.Connection
Set rs2 = New ADODB.Recordset

conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\atm.mdb;" & _
"Persist Security Info=False"

'* update balance information for user
rs2.Open "UPDATE Users SET Balance = " & balanceleft & "WHERE userID = " & txtuserID.Text, _
conn2, adOpenForwardOnly, adLockReadOnly, adCmdText

rs2.Update

'* close rs connection
rs2.Close
Set rs2 = Nothing




I'm having problems getting the above code to update the database, any ideas? thanks

First (and without going through your syntax) use connection object to execute update sql statement:

strSQL = "Update ..."
conn2.Execute strSQL

You may need to Resynq your recordset if it's already open:

RS2.Resync adAffectAllChapters, adResyncAllValues

What's the error?

Is Balance a string or a number?

For a start, I think you've missed a space character before the WHERE statement which will make your SQL invalid.
Should be: ... balanceleft & " WHERE userID = " & ...

balanceleft is a number, the error says operation not allowed while connection is closed..It now updates the database tho lol

Try this one .it will solve ur problem

Set cn = New ADODB.Connection

if cn.state=adstateopen then cn.close
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\atm.mdb;" & _
"Persist Security Info=False"

'* update balance information for user
cn.execute "UPDATE Users SET Balance = " & balanceleft & "WHERE userID = " & txtuserID.Text


code by ashish sharma

Quote:

---

Originally Posted by ashish sharma

Try this one .it will solve ur problem

Set cn = New ADODB.Connection

if cn.state=adstateopen then cn.close
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\atm.mdb;" & _
"Persist Security Info=False"

'* update balance information for user
cn.execute "UPDATE Users SET Balance = " & balanceleft & "WHERE userID = " & txtuserID.Text


code by ashish sharma

---

Do you Ashish ever read replies before posting your own ???

Here is what I suggested more than an hour before you did :rolleyes:

strSQL = "Update ..."
conn2.Execute strSQL

thejoker,

90% of your SQL Statement problems will go away if you use code similar to what RhinoBull suggest and after doing a statement like

strSQL = "Update ..."

You do a

Debug.Print strSQL

You generally will see obvious typos, Bad SQL syntax and problems of that sort.

thanks a lot for the help guys