how to query a table?

Question

Suppose we have one table in Microsoft Access:

ID | Data

I need to select one row with a SQL query in VBA code.

In what variable to store that row? How to write that row back to the table in VBA? Could you please write simple code?

Answer 1

You need to use a RecordSet object. This site provides many examples.

Here's a an example I put together from that site. This will grab all the records from tblName and update just the first record

UPDATE Answer changed to use DAO which is recommended when using Access Tables.

Dim rs As New DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT pkPeopleID, LastName FROM tblPeople", dbOpenDynaset)

rs.MoveFirst
rs.Edit
rs![Data] = "Foobar"
rs.Update

rs.Close
Set rs = Nothing

If you want a specific row rather than just the first row you can add a where clause e.g.

 Set rs = CurrentDb.OpenRecordset("SELECT pkPeopleID, LastName FROM tblPeople Where id = 123", dbOpenDynaset)

Or you could use FindFirst instead

rs.FindFirst "id = 123"