Search code examples
sqlvbams-access

Run multiple SQL UPDATE strings using VBA but getting error

I've spent the weekend scouring Stack Overflow for an answer and I can't find one that addresses my specific issue. Below is the SQL string of a simple Update query. I have ten of these separate queries. I can easily use VBA to run each query in succession, and that works great. BUT, I would prefer to run them using RunSQL so I don't have to have 10 separate queries to manage. I took the SQL string from the Query Designer and pasted it to the following VBA:

Dim sql As String
sql = "UPDATE Comments LEFT JOIN KeysFound ON Comments.[Survey#] = KeysFound.[survey#] _
SET KeysFound.Lost = -1, KeysFound.[survey#] = [Comments].[survey#] _
WHERE (((Comments.Comment) Like " * Lost * "))"
DoCmd.RunSQL (sql)

Here's what happens:

screenshot of sql string and error

enter image description here

Screenshot of simple Query Design

I removed line breaks; still error. I added a semi-colon to end and still error. I thought running the simple SQL statement from the Query Designer would not error out.

The goal is to run 10 of these statements under one VBA Procedure so I don't carry 10 separate queries. The only thing that changes in the SQL statement is the Like "Lost" The word Lost will be replaced with "Unhappy" and then replaced with "slow" and so on. Then the UPDATE statement simply updates the associated row in the joined table with a -1 in the "Yes/No" field when the word is found in the comments.

This works great using separate queries and running them in sequence with the Docmd.openquery, but that's not the goal.

Result of array

Result for field.lost; need to change to next el

Good result

Solution

  • Your sql construction is off - see suggestions below

    Dim sql As String, el
For Each el in Array("Lost", "Unhappy", "slow") 'loop search terms
    sql = "UPDATE Comments LEFT JOIN KeysFound ON Comments.[Survey#] = KeysFound.[survey#] " & _
          " SET KeysFound." & el & " = -1, KeysFound.[survey#] = [Comments].[survey#] " & _
          " WHERE Comments.Comment Like '*" & el & "*'"
    DoCmd.RunSQL sql
Next el