How to Update a Query in Access VBA

Knowing how to update data in a query in Microsoft Office Access using Visual Basic for Applications (VBA) can save you time when you need to continuously update data in tables. Access is a relational database management system commonly used for smaller local databases. A query is used to view data in a database table. VBA is a computer programming language used to automate routine tasks in Access.

Instructions

1. Start Microsoft Office Access, select "Database Tools" and click "Visual Basic." Select the "Insert" menu, then click "Module."

2. Copy and paste the following code into your module:

   Private Sub updateQuery()

   Dim db As Database

   Dim rst As Recordset

   Dim sQLString As String

   Dim rstCnt As Integer

   Set db = CurrentDb

   sQLString = "CREATE TABLE tableToUpdate (First TEXT, Last TEXT)"

   DoCmd.SetWarnings False

   DoCmd.RunSQL (sQLString)

   strsql = "INSERT INTO tableToUpdate VALUES('Oscar','Gonzalez')"

   DoCmd.RunSQL (strsql)

   strsql = "INSERT INTO tableToUpdate VALUES('Kitzia','Ramos')"

   DoCmd.RunSQL (strsql)

   strsql = "INSERT INTO tableToUpdate VALUES('John','Smith')"

   DoCmd.RunSQL (strsql)

   strsql = "INSERT INTO tableToUpdate VALUES('Anna','Williams')"

   DoCmd.RunSQL (strsql)

   Set rst = db.OpenRecordset("SELECT tableToUpdate.* FROM tableToUpdate;")

   rst.MoveLast

   rst.MoveFirst

   For rstCnt = 0 To rst.RecordCount - 1

   If rst.Fields(0).Value = "Oscar" Then

   rst.Edit

   rst.Fields(0).Value = "Emilio"

   rst.Update

   End If

   rst.MoveNext

   Next rstCnt

   End Sub

3. Press "F5" to run the "updateQuery" subroutine. The subroutine creates a new table, inserts new rows of data, queries the table created and updates the first field.

References

• Microsoft: How to--Sort Data in a DAO Recordset