Error: Missing Operator in Query Expression VBA

Question

I'm writing VBA to loop through excel files and upload the data to an access crime db.

My template sql statement is:

sql = "insert into crimedata(reportYear, state, school, campus, dataLabel, dataValue)" & _ 
      "values(<reportYear>, <state>, <school>, <campus>, <dataLabel>, <dataValue>);"

My logic for building the query and executing it are as follows:

sql = Replace(sql, "<dataValue>", s.Cells(i, c).value)
sql = Replace(sql, "<reportYear>", Year)
sql = Replace(sql, "<state>", State)
sql = Replace(sql, "<school>", s.Cells(i, 1).value)
    If s.Cells(i, 2).value = "" Then
       sql = Replace(sql, "<campus>", "null")
    Else
       sql = Replace(sql, "<campus>", s.Cells(i, 2).value)
    End If
sql = Replace(sql, "<dataLabel>", s.Cells(5, c).value)
cn.Execute sql

When I remove cn.Execute sql and Debug.Print sql after each loop I get statements like these:

insert into crimedata(reportYear, state, school, campus, dataLabel, dataValue)values(2011, ALASKA, University of Alaska, Fairbanks, Larceny, 64);
    
insert into crimedata(reportYear, state, school, campus, dataLabel, dataValue)values(2011, ALASKA, University of Alaska, Fairbanks, Autotheft, 3);

However, when I add cn.Execute sql to execute the query, I get the following error:

Run-time error '-2147217900 (80040e14)': Syntax error (missing operator) in query expression 'University of Alaska'

I'm unsure what I'm missing syntax wise?

Pᴇʜ Pᴇʜ · Accepted Answer · 2020-11-24T07:34:50

Your values need to be in quotes like below if they are text:

values(2011, 'ALASKA', 'University of Alaska', 'Fairbanks', 'Larceny', 64)

so adjust here:

sql = "INSERT INTO crimedata (reportYear, state, school, campus, dataLabel, dataValue)" & _ 
      "VALUES (<reportYear>, '<state>', '<school>', '<campus>', '<dataLabel>', <dataValue>);"

If your fields reportYear and dataValue defined as text fields too, then wrap them into quotes '' too, if they are defined as number fields then don't.

Error: Missing Operator in Query Expression VBA

1 Answers