Invalid Argument Error: MSAccess and SQL

Question

I am trying to access certain lines from my SQL database from MSAccess and I keep getting an Invalid Argument Error on this line:

Set rs = CurrentDb.OpenRecordset("SELECT TimeID " & _
    "FROM tblLunchTime " & _
    "WHERE ProductionID = prodSelect AND EndTime is NULL AND StartTime < dateAdd('h', 3, NOW())", [dbSeeChanges])

Is something not right in this?

Private Sub cmdClockEnd_Click()

'Check if a group has been selected.
If frmChoice.value = 0 Then

    MsgBox "Please select a production line."

    End

End If

'Setup form for user input.
lblEnd.Visible = True

'Save end of lunch value.
lblEnd.Caption = Format(Now, "MMM/DD/YY hh:mm:ss AMPM")

'Declare database variables.
Dim dbName As DAO.Database
Dim strValuesQuery As String
Dim rs As DAO.Recordset
Dim prodSelect As String
Dim sSQL As String
Dim timeValue As String
Set dbName = CurrentDb

'Get values of Production Line.
If frmChoice.value = 1 Then

prodSelect = "L2"

ElseIf frmChoice.value = 2 Then

prodSelect = "L3"

End If

'Get the last TimeID with the following parameters.
sSQL = "SELECT TimeID " & _
    "FROM tblLunchTime " & _
    "WHERE ProductionID = prodSelect AND EndTime is NULL AND StartTime < #" & DateAdd("h", 3, Now()) & "#"

Set rs = dbName.OpenRecordset(sSQL, dbSeeChanges)

strValuesQuery = _
                    "UPDATE tblLunchTime " & _
                    "SET EndTime = '" & Now & "'" & _
                    "WHERE TimeID = " & rs![TimeID] & " "

'Turn warning messages off.
DoCmd.SetWarnings False

'Execute Query.
DoCmd.RunSQL strValuesQuery

'Turn warning messages back on.
DoCmd.SetWarnings True

End Sub

Answer 1

You need to put prodSelect outside the quotes:

"WHERE ProductionID = " & prodSelect & " AND ...

It is nearly always best to say:

sSQL="SELECT TimeID " & _
    "FROM tblLunchTime " & _
    "WHERE ProductionID = " & prodSelect & _
    " AND EndTime is NULL AND StartTime < dateAdd('h', 3, NOW())"
''Debug.print sSQL
Set rs = CurrentDb.OpenRecordset(sSQL)

You can see the advantage in the use of Debug.Print.

AHA prodSelect is text! You need quotes!

sSQL="SELECT TimeID " & _
    "FROM tblLunchTime " & _
    "WHERE ProductionID = '" & prodSelect & _
    "' AND EndTime is NULL AND StartTime < dateAdd('h', 3, NOW())"

Answer 2

I think I would do the date criterion concatenation client-side, too, since it's one more thing that could go wrong:

  "...StartTime < #" & DateAdd("h", 3, Now()) & "#"

I don't know that SQL Server doesn't have DateAdd() and Now() function nor that they don't behave exactly the same as in Access, but I wouldn't take the chance -- I'd do this calculation on the client instead of handing it off to the server.

Answer 3

There appears to be confusion about tblLunchTime ... whether it is a native Jet/ACE table or a link to a table in another database. Please show us the output from this command:

Debug.Print CurrentDb.TableDefs("tblLunchTime").Connect

You can paste that line into the Immediate Window and press the enter key to display the response. (You can open the Immediate Window with CTRL+g keystroke combination.)

Just in case the response starts with "ODBC", suggest you try this line in your code:

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

Update: Now that you're past that hurdle, suggest you change your approach with the UPDATE statement. Don't turn off warnings; try something like this instead:

'Execute Query. '
CurrentDb.Execute strValuesQuery, dbFailOnError

And add an error handler to deal with any errors captured by dbFailOnError.