Using Recordsets in Access 2007

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What would the code look like in Access 2007 if, in VBA code, I wanted to
open a recordset of a table called "ACCOUNT" and update a field named
[address].
 
See:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

To modify the field, use your field and table names in the SQL string, and
replace the line:
Debug.Print rs!MyField
with:
rs.Edit
rs!address = 99
rs.Update

Note that the code loops through all the records and would replace them all.
To modify fewer records, add a WHERE clause to the SQL statement.

You could also do this with an update query, e.g.:
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb()
strSql = "UPDATE ACCOUNT SET address = 99 WHERE ID = 767;"
db.Execute strSql, dbFailOnError
Debug.Print db.RecordsAffected & " record(s) updated."
Set db = Nothing
 
Back
Top