VBA Excel Syntax Error Missing Operator in Query Expression Insert Statement

Question

The premise behind this program is to take several different excel sheets and input them in to an access database. Everything is working until my execute statement which produces the following error:

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

The problem seems to arise at the portion of my execute statement that deals with the "school" variable. below is the code.

Sub updateData()

Dim cn As New ADODB.Connection
Dim row As Integer
Dim col As Integer
Dim srcRow As Integer
Dim srcCol As Integer
Dim filename As String
Dim targetWorkbook As Workbook
Dim targetSheet As Worksheet
Dim reportYear As String
Dim state As String
Dim school As String
Dim campus As String
Dim dataLabel As String
Dim dataValue As Long


On Error Resume Next ' these lines should allow you to connect.  Depending on your version of windows, only one will execute without error.
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\universityCrime.mdb" & ";"
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\universityCrime.mdb" & ";"
On Error GoTo 0

'Starting here!
row = 6
col = 3
srcRow = 1
srcCol = 1

Application.ScreenUpdating = False

filename = Dir(ThisWorkbook.Path & "\data\*.xls")

'Loops through all the file names in the data directory
Do Until filename = ""

    Set sourceWorkbook = Application.Workbooks.Open(ThisWorkbook.Path & "\data\" & filename)
    Set sourceSheet = sourceWorkbook.Sheets(1)


    reportYear = Right(sourceSheet.Range("A4").Value, 4)
    state = Left(sourceSheet.Range("A2").Value, 1) & LCase(Mid(sourceSheet.Range("A2").Value, 2))

    Do Until sourceSheet.Cells(row, 1).Borders(xlEdgeBottom).LineStyle = 1
        school = sourceSheet.Cells(row, 1).Value
        campus = sourceSheet.Cells(row, 2).Value

        Do Until sourceSheet.Cells(row, col) = ""
            dataLabel = sourceSheet.Cells(5, col).Value
            dataValue = sourceSheet.Cells(row, col).Value

            If campus = "" Then
                cn.Execute "insert into crimedata(reportYear, state, school, campus, dataLabel, dataValue)values(" _
                & reportYear & ", " _
                & state & ", " _
                & school & ", " _
                & campus & ", " _
                & dataLabel & ", " _
                & dataValue & ")"
            Else:
                cn.Execute "insert into crimedata(reportYear, state, school, campus, dataLabel, dataValue)values(" _
                & reportYear & ", " _
                & state & ", " _
                & school & ", " _
                & "NULL, " _
                & dataLabel & ", " _
                & dataValue & ")"
            End If

            srcCol = 1
            srcRow = srcRow + 1
            col = col + 1
        Loop

        col = 3
        row = row + 1
    Loop

    row = 6
    sourceWorkbook.Close
    filename = Dir
Loop

cn.Close
Application.ScreenUpdating = True

End Sub

Thank you!

Answer 1

So @Omnikrys and @patrick choi did a great thing by helping me. It turns out all I needed was the single quotes around the execute statement. So instead of my execute statement looking like this:

cn.Execute "insert into crimedata(reportYear, state, school, campus, dataLabel, dataValue)values(" _
            & reportYear & ", " _
            & state & ", " _
            & school & ", " _
            & campus & ", " _
            & dataLabel & ", " _
            & dataValue & ")"

I had to add single quotes between everything that happened to be a text variable like this:

cn.Execute "insert into crimedata(reportYear, state, school, campus, dataLabel, dataValue) values(" _
                & reportYear & ", '" _
                & state & "', '" _
                & school & "', '" _
                & campus & "', '" _
                & dataLabel & "', " _
                & dataValue & ")"

Thanks for the tips guys! It helped.