Add, Update and Remove Records from any Access Table using SQL

Most Access developers (and DAO users in general) should already be familiar with the use of Recordsets and their corresponding methods for adding records to a table. All of the following examples assume that you have set a reference to the DAO Object Library.

To add new records to a table, all you need to do is

Create a Recordset object based on the table
Call the AddNew method to add a new record
Specify the values of each of the fields in the record
Call the Update method to commit the update
Close the Recordset (optional)

Assume a table called "tblCustomers" with two fields, "CustomerName" and "CustomerID". To add a record to this table using DAO, the code would be

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblCustomers")

With rs
  .AddNew

  ![CustomerName] = "My Customer Name"
  ![CustomerID] = "123"

  .Update
End With
' rs.Close

But as I've discovered recently, you can also use SQL to add records to a table. After practicing with the syntax, I came up with the following procedures to add, remove and update records in any table in your database.

Add a single record to a given table

The major benefit of this function is that it builds the field names automatically based on the table, so you don't have to write hard-coded SQL INSERT statements that need to be changed if you rearrange the fields in a table or add new fields.

Function AddRecord(tableName As String, values() As Variant)
' append a record to a table

  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim sqlString As String
  Dim i As Long

  ' grab recordset so we can read fields automatically
  Set rs = GetRecordset(tableName)

  sqlString = "INSERT INTO " & tableName & " ("

  ' read field names
  ' don't append a comma on the last field name
  For i = 0 To rs.Fields.Count - 1
    If i = rs.Fields.Count - 1 Then
      sqlString = sqlString & rs.Fields(i).Name
    Else
      sqlString = sqlString & rs.Fields(i).Name & ", "
    End If
  Next i

  ' close field names list and start values section
  sqlString = sqlString & ") VALUES ("

  ' read values from array
  ' don't append a comma on the last value
  For i = LBound(values) To UBound(values)
    If i = UBound(values) Then
      If IsNumeric(values(i)) Then
        sqlString = sqlString & values(i)
      Else
        sqlString = sqlString & "'" & values(i) & "'"
      End If
    Else
      If IsNumeric(values(i)) Then
        sqlString = sqlString & values(i) & ", "
      Else
        sqlString = sqlString & "'" & values(i) & "', "
      End If
    End If
  Next i

  ' finish SQL string
  sqlString = sqlString & ");"

  Debug.Print sqlString

  Call ExecSQL(sqlString)

End Function

As a second parameter, AddRecord takes a Variant which should contain the values to be written to the table, in the same order as the fields in the table. Using our fictitious table above, the call to this function would be

Dim values() As Variant
values = Array("My Customer Name","123")
Call AddRecord("tblCustomers", values)

because starting from the left, CustomerName is the first field and CustomerID is next.

Even better is when you read these values from form fields, so you can design forms that accept user input to update tables. In the example above, a Customers form that lets you add new customer information.

Dim values() As Variant
' grab values from a textbox and listbox on a form
' textbox prompts for customer name, listbox has customer IDs
values = Array(Me.text0.Value,Me.list0.Value)
Call AddRecord("tblCustomers", values)

Remove matching records from a table

Specify the table name, the field you want to check for matching records, and the matching value to be removed, and this procedure will remove all matching records in the table. The procedure ExecSQL (see Utility Routines below) does the actual running of the generated SQL statement.

Function RemoveRecords(tableName As String, fieldName As String, _
    criteria As Variant)
' delete a record or set of records from tableName matching criteria in fieldName

  Dim sqlString As String

  Select Case TypeName(criteria)
    Case "String"
      sqlString = "DELETE FROM " & tableName & " WHERE " & _
    fieldName & " = " & "'" & criteria & "'"
    Case Else
      sqlString = "DELETE FROM " & tableName & " WHERE " & _
    fieldName & " = " & criteria & ""
  End Select

  Debug.Print sqlString

  Call ExecSQL(sqlString)

End Function

Update matching records in any table

The SQL UPDATE statement will update matching records in the specified table. All you need to do is pass the table name, the field to be checked, the value that should be changed and the new value it should be changed to, to the UpdateRecords function.

Numeric values are passed as is, while other values are surrounded by single quotes. I have yet to test this out with non-String values, I'd be curious to know if it works with single quotes or if a Select Case statement is needed.

Function UpdateRecords(tableName As String, fieldName As String, _
    oldVal As Variant, newVal As Variant)
' update a record or set of records from tableName matching criteria in fieldName

  Dim sqlString As String

  If IsNumeric(oldVal) Then
    sqlString = "UPDATE " & tableName & " SET " & fieldName & _
    " = " & newVal & " WHERE " & fieldName & " = " & oldVal
  Else
    sqlString = "UPDATE " & tableName & " SET " & fieldName & _
    " = '" & newVal & "' WHERE " & fieldName & " = '" & oldVal & "'"
  End If

  Debug.Print sqlString

  Call ExecSQL(sqlString)

End Function

Utility routines

These functions are called from the above procedures (and from each other), so they should be pasted into the same project as the ones above. Usually what I do is insert a standard module (call it "Utils") and put these types of routines there.

ExecSQL allows you to run any SQL statement, specifically action queries like the ones above. GetRecordset returns a DAO.Recordset object based on the table name passed to it. GetCurrentDB returns a reference to the current database.

Function ExecSQL(strSQL As String, Optional warn As Boolean = False)
' run any SQL statement

  With DoCmd
    .SetWarnings warn
    .RunSQL strSQL
    .SetWarnings (Not warn)
  End With

End Function

Function GetRecordset(tableName As String) As DAO.Recordset
' return recordset from specified table to calling procedure

  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset

  Set dbs = GetCurrentDB
  Set GetRecordset = dbs.OpenRecordset(tableName, dbOpenDynaset)

End Function

Function GetCurrentDB() As DAO.Recordset
  GetCurrentDB = CurrentDb
End Function