I am currently writing code in order to add , modify (edit), and delete records.

i have few text box holding data. besides that, i am using ADODB to connect to my database. I am able to connect and retrieve records from tables. but I couldnt add new record, update modified record and delete record.

When this program running, it showed no problems in all this operations, there is totally no error. but when i check back my database, there is no record has been added or updated.

The following is some of my code:

Dim cn As New ADODB.connection
Dim rs As New ADODB.Recordset
Dim strsql As String

Private Sub Form_Load()
On Error Resume Next

     'Create Connection Object
    cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.3.51;" & _
                          "data source=C:\data.mdb;"
    cn.Open

    rs.CursorLocation = adUseClient

    strsql = "SELECT * FROM data;"

    'Create Recordset Object
    rs.Open strsql, cn, adOpenKeyset, adLockBatchOptimistic
    Set rs.ActiveConnection = Nothing

End Sub

'Add new record
Private Sub Add_Click()
On Error Resume Next

    tbRecordNo.text = rs.RecordCount + 1

End Sub

Private Sub Save_Click()
            rs.AddNew
End Sub

Private Sub Delete_Click()
On Error Resume Next
        rs.Delete
End Sub

Above is some of my code. In addition, I got no idea on how to modify and update my records.

Advanced thanks to those who able to help me.

You set the recordset's connection to nothing right after you open it, so it is no longer connected to the database. If it is not connected to the database, then no updates to it will be written through to that database...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

you can do this by two ways:
1.set recordset's field property like this:
rst("fieldname1")=textbox1.text
...
rst("fieldnamen")=textboxn.text
then,call rst.update method.
2.send sql to DBMS:
if you want to add a new record,
strSQL="INSERT INTO tablename(field1,...,fieldn) VALUES(value1,...valuen)"
if you want to modify a exist record,
strSQL="UPDATE tablename SET field1=value1,...,fieldn=valuen"
then call Command object's Execute method like this:
oCmd.CommandText = strSQL
oCmd.Execute

Do not use
"On Error Resume Next"
You need to at least get the error so that you know where to trouble shoot.




Sal

I usually use this code for unbound forms:

rs.AddNew
rs!field1 = Trim(Text1)
rs!field2 = Trim(Text2)
rs.Update
rs.Close
Set rs = Nothing

I know this was posted last year but i thought it might help someone in the future to know what the actual answer to the question was.

You wanted to know why when you opened the recordset using
something of the nature:
sql = "SELECT * FROM <sometable>"

why it does not update the table <sometable> when you make changes to the recordset and why know errors appear either.

THE MAGIC ANSWER---
Your recordset is not that of the table it is a copied portion of the table (even if it is a complete copy). In order to edit a table using ADODB recordsets you must open the table

eg:
Dim condatabase As New ADODB.connection
Dim rst As New ADODB.recordSet
Set condatabase = CurrentProject.connection
rst.ActiveConnection = condatabase

'Open Recordset Object
rst.Open <sometablename>, cn, adOpenKeyset, adLockBatchOptimistic


Hence we are now editing the table and not a copy of it.
Hope this helps someone.