Access VB Error 3061: too few parameters, expected 1

Question

I am writing a simple piece of SQL query to update a table in my test database, but I am encountering problems:

Public Sub UpdateStatus(TypeName As String)
Dim DBase As Database
Dim SQLCommand As String
Dim qdfChange As QueryDef
SQLCommand = "Update Case SET Status = 1 WHERE TypeName = '" & TypeName & "';"
Debug.Print SQLCommand

Set DBase = OpenDatabase("C:\TestDatabase\CaseSet.accdb")
Set qdfChange = DBase.CreateQueryDef("", SQLCommand)
qdfChange.Execute
End Sub

The field names in table Case match the ones in my SQL query.

(If this is of any value, this function belongs to a form)

Are you able to run your SQL directly? Is your actual column/table names a reserved word in Access? What data type is bar and foo? — JNevill
Is bar getting properly set? Can you put a breakpoint on the SQLCommand= line and verify that bar has a value? — JNevill
Is table name, field1? Can you actually put the SQL statement which should not reveal personal/proprietary data? — Parfait

HansUp HansUp · Accepted Answer · 2016-05-23T19:26:49

Case is a reserved word and TypeName is a VBA Function. That makes them poor choices for database object names.

If you can't rename them, bracket those names in your SQL statement so the db engine will recognize them as object names.

SQLCommand = "Update [Case] SET Status = 1 WHERE [TypeName] = '" & TypeName & "';"

Access VB Error 3061: too few parameters, expected 1

1 Answers