Using AI to Write Microsoft Access VBA

What the AI Needs to Know

• That it is Access, not Excel

  Otherwise you get Range and Worksheet objects that do not exist in Access.

• The form and control names

  frmOrders with txtCustomerID, cboStatus, cmdSave, and the subform control name, not just the subform's form name.

• Tables and key fields

  Which table the form is bound to and the primary key.

• Where the data lives

  Local tables, linked Access back end on a network share, or linked SQL Server tables. Some techniques behave differently.

• Who uses it

  One person, or several at once. Multi-user code needs record-locking awareness.

A Prompt Template That Works

Microsoft Access 365 VBA (DAO). Split database; tables linked from
\\server\data\Ops_BE.accdb. 5–8 concurrent users.
Form frmOrders, bound to tblOrders (PK OrderID).
Controls: cboCustomerID, txtOrderDate, cboStatus, cmdApprove.
Task: when cmdApprove is clicked:
  1. If cboStatus is not "Pending", show a message and stop.
  2. Set Status to "Approved", ApprovedBy to the Windows user name,
     ApprovedOn to Now().
  3. Insert a row in tblOrderLog (OrderID, Action, User, LoggedAt).
  4. Save the record and refresh the form.
Rules: Option Explicit, error handler, CurrentDb.Execute with
dbFailOnError, no DoCmd.SetWarnings. Comment each block.

What Good Access VBA Looks Like

Option Compare Database
Option Explicit

Private Sub cmdApprove_Click()
    On Error GoTo Fail
    Dim db As DAO.Database
    Dim userName As String

    If Nz(Me.cboStatus, "") <> "Pending" Then
        MsgBox "Only Pending orders can be approved.", vbExclamation
        Exit Sub
    End If

    ' Save any unsaved edits first so we update the current values
    If Me.Dirty Then Me.Dirty = False

    userName = Environ("USERNAME")
    Set db = CurrentDb

    db.Execute "UPDATE tblOrders SET Status = 'Approved', " & _
               "ApprovedBy = '" & Replace(userName, "'", "''") & "', " & _
               "ApprovedOn = Now() WHERE OrderID = " & Me.OrderID, dbFailOnError

    db.Execute "INSERT INTO tblOrderLog (OrderID, Action, [User], LoggedAt) " & _
               "VALUES (" & Me.OrderID & ", 'Approved', '" & _
               Replace(userName, "'", "''") & "', Now())", dbFailOnError

    Me.Requery
    MsgBox "Order " & Me.OrderID & " approved.", vbInformation

Done:
    Set db = Nothing
    Exit Sub
Fail:
    MsgBox "Could not approve order: " & Err.Description, vbCritical
    Resume Done
End Sub

• Me.Dirty = False saves pending edits, avoiding a write conflict between the form and the UPDATE.

• dbFailOnError raises an error instead of silently skipping locked or invalid rows.

• Replace(..., "'", "''") prevents a user name with an apostrophe from breaking the SQL.

• [User] is bracketed because User is a reserved word in Access SQL.

Recordset Loop Pattern AI Should Follow

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset( _
    "SELECT OrderID, Total FROM tblOrders WHERE Status = 'Open'", _
    dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
    If rs!Total > 10000 Then
        rs.Edit
        rs!NeedsReview = True
        rs.Update
    End If
    rs.MoveNext
Loop
rs.Close: Set rs = Nothing

Review Checklist for AI-Written Access Code

• Compile first

  Debug → Compile catches undeclared variables, wrong control names and missing references before anything runs.

• Search for DoCmd.SetWarnings

  Replace with CurrentDb.Execute ... dbFailOnError.

• Search for DELETE and DROP

  Confirm the WHERE clause, test it as a SELECT.

• Check string-built SQL

  Text values need quotes and apostrophe escaping; dates need # and a safe format such as Format(d, "\#yyyy-mm-dd\#").

• Check every procedure has an error handler

  Unhandled errors in Access Runtime close the application.

• Check object cleanup

  Recordsets closed, objects set to Nothing, especially in loops.

Errors AI-Written Access VBA Causes Most

Using AI to Understand Inherited Code

• Paste a module and ask: what does each procedure do, which tables does it read and write, and what could it delete?

• Ask for a list of every form, report and query the code references.

• Ask it to add comments without changing behaviour, then diff the result to confirm.

• Ask where the code would break if the back end moved to SQL Server.