Access Update QueryDef Parameters not found

Question

I've two queries I'm trying to create with QueryDef.

This insert query works perfectly and I can assign the parameters:

Dim qryAddPackage As String: qryAddPackage = "" & _
    "INSERT INTO `RMF_tblPackages` " & _
    "(`SystemID`, `DoD`, `DateCreated`, `LastModified`, `LastUpdatedBy`) " & _
    "VALUES ([SystemID], [DoD], NOW(), NOW(), [UID])"

With CurrentDb.CreateQueryDef(vbNullString, qryAddPackage)
    .Parameters!SystemID.Value = cbbSystems.Value
    .Parameters!DoD.Value = Me.txtDoD.Value
    .Parameters!UID.Value = strGetUserID()
    .Execute
End With

However, in this update query, I am only able to access the Parameter UID in the WHERE statement:

Dim qryUpdatePkg As String: qryUpdatePkg = "" & _
"UPDATE `RMF_tblPackages` " & _
"SET " & _
    "`SystemID` = [SystemID], `DoD` = [DoD], `LastModified` = NOW(), `LastUpdatedBy` = [UID] " & _
" WHERE `PackageID` = [PackageID]"

With CurrentDb.CreateQueryDef(vbNullString, qryUpdatePkg)
    .Parameters("SystemID").Value = cbbSystems.Value
    .Parameters!DoD.Value = Me.txtDoD.Value
    .Parameters!UID.Value = strGetUserID()
    .Parameters!PackageID.Value = Me.lbxPackages.Value
    .Execute dbSeeChanges
End With

In every other line trying to assign a parameter, it just says that the parameter's name is not in the collection.

I've tried assigning them like this:

.Parameters("PackageID").Value = Me.lbxPackages.Value

As well, but that doesn't work either.

I tried just running the update query with some data and it worked fine so the query works.

.Params.count returns 1 as you would expect given it's only recognizing one param.

Any other ideas as to why the parameters aren't being recognized by that update query?

This SQL looks like MySQL syntax, not Access SQL.

Gustav
– Gustav

2019-07-15 20:32:37 +00:00
Commented Jul 15, 2019 at 20:32 — Gustav
– Gustav, Commented Jul 15, 2019 at 20:32

Mathieu Guindon · Accepted Answer · 2019-07-15 20:56:40Z

2

Use [ ] for quoting identifiers. In Access the backtick is not valid (unlike MySQL). Also column names and parameter names use the same quoting characters, so you must choose different names for the parameters. Quoting identifiers is only necessary if the names collide with a reserved word or function name (like Date, From, By, Order etc).

Dim qryUpdatePkg As String

qryUpdatePkg = "UPDATE RMF_tblPackages " & _
"SET " & _
  "SystemID = prmSystemID, DoD = prmDoD, LastModified = Now(), LastUpdatedBy = prmUID " & _
" WHERE PackageID = prmPackageID"

With CurrentDb.CreateQueryDef(vbNullString, qryUpdatePkg)
    .Parameters("prmSystemID").Value = cbbSystems.Value
    .Parameters!prmDoD.Value = Me.txtDoD.Value
    .Parameters!prmUID.Value = strGetUserID()
    .Parameters!prmPackageID.Value = Me.lbxPackages.Value
    .Execute dbSeeChanges
End With

Do the same changes in the first query.

edited Jul 15, 2019 at 20:56

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Jul 15, 2019 at 20:42

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

Hope you don't mind, I corrected "escapes" to "quoted identifiers" terminology; "escapes" has more to do with e.g. doubling single-quote characters inside a string literal.

Collectives™ on Stack Overflow

Access Update QueryDef Parameters not found

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related