The Linux Page

Read/Write Cursor in MS-Access using VBA

When you want to modify a set of rows in a table but need to do some computation as you go, you probably want to use a cursor. That's my first idea. Whenever possible I do an UPDATE, but in one case that was just way too heavy (i.e. one SELECT plus one UPDATE per row!) Of course, in the end, the result is the same, but it is much less tedious to use a cursor.

How to use a cursor is very well explained in the documentation once you know the functions that you need to use to make it work. I only knew that the Recordset could be used that way.

The fact is that you need to build a SELECT command, open your record set, then on each row do an Edit, do your updates and finally do Update. That's this simple. But as an advanced database user, such as PostgreSQL, that's not the way I was thinking it would work...

There is a snippet of code that does the job:

  ' A simple SQL statement
  sql = "SELECT * FROM Customer" 
  ' Create the Recordset
  Set rcd = CurrentDb.OpenRecorset(sql)
  Do While Not rcd.EOF
    If rcd("Age") > 30 Then       ' Prepare for update

      ' Now we can update
      rcd("Salary") = rcd("Salary") * 2

      ' The row is ready to be saved
    End If 
    ' Go to the next record