Error 3061 when running update query from code

[Rob Parker]

I am attempting to run an update query (a saved query in my database)
from code, and keep getting an error message (3061, Too few
parameters, expected 2).

The SQL of the query is:
UPDATE DAT_Arisings_Remarks SET DAT_Arisings_Remarks.ImageFile =
ChangeFileLink([ImageFile],[Forms]![frmSetupDatabase]![txtOldFolder],
[Forms]![frmSetupDatabase]![txtFolder])
WHERE (((Left([ImageFile],Len([Forms]![frmSetupDatabase]!
[txtOldFolder])))=[Forms]![frmSetupDatabase]![txtOldFolder]))
WITH OWNERACCESS OPTION;

It is designed to change portion of a path/filename from the string in
txtOldfolder to the string in txtFolder; it gets these values from
textbox controls on frmSetupDatabase. It runs perfectly well from the
database window, but fails when I try to run it from code.

My initial code was:
CurrentDb.Execute "qryChangeLinks_DAT_Arisings_Remarks",
dbFailOnError

I've tried adding a parameter statement to the saved query:
PARAMETERS [Forms]![frmSetupDatabase]![txtOldFolder] Text ( 255 ),
[Forms]![frmSetupDatabase]![txtFolder] Text ( 255 );

Again, the query runs fine from the database window, but gives error
3061 when run from code.

I've replaced the saved query name in the code with a string
containing the SQL of the query (both with and without the Parameters
clause), in this fashion:
strSQL = "UPDATE ... "
CurrentDb.Execute strSQL, dbFailOnError
and it still fails. The SQL string is correct; if I print it to the
immediate window, then cut/paste to a new query, it runs OK.

I've even tried replacing CurrentDb with:
Dim db As Database
Set db = DBEngine(0)(0)
db.Execute ...
[I don't think this should be necessary, but I'm trying everything I
can think of] Same error.

What is going on here? I'm at a complete loss.

TIA,

Rob

 

[Rob Parker]

Some additional information:

If I remove the WHERE clause from my query, I still get the error. So
I presume that it's coming from the references to the two textbox
controls in the ChangeFileLink function.

This function simply changes the left portion of the path/filename;
its code (error handler removed for conciseness) is as follows:
Public Function ChangeFileLink(varOldLink As Variant, varOldPath As
Variant, varNewPath As Variant) As Variant
' declared as variant, to allow nulls
Dim strPart As String
If IsNull(varOldLink) Or Len(varOldLink) = 0 Then
ChangeFileLink = varOldLink
Else
strPart = Right(varOldLink, (Len(varOldLink) - Len(varOldPath)))
ChangeFileLink = varNewPath & strPart
End If
End Function

Again, the simplified query runs from the database window, but not
from code.

Rob

 

[Dirk Goldgar]

In

Some additional information:

If I remove the WHERE clause from my query, I still get the error. So
I presume that it's coming from the references to the two textbox
controls in the ChangeFileLink function.

Right. You see, when you use CurrentDb.Execute to run an action query,
or CurrentDb.OpenRecordset to run a select query, you're asking DAO to
interpret and process the query all by itself, without any preliminary
handling by Access. But DAO doesn't know anything about forms and
controls. As far as DAO is concerned, those form & control references
are just parameters that need to be filled in with values before the
query can be executed.

When *Access* runs the query, it looks for parameters that it can
interpret as form & control references, and fills in the values
automatically before handing the query off to DAO to execute. Any
parameters it can't resolve that way, it prompts you for. But your code
is using DAO, and Access isn't involved, so you have to resolve the
parameters yourself, or else eliminate them.

If this were an inline SQL string, you could just embed the control
values directly in the string. But since this is a saved query, you can
use a trick involving the DAO QueryDef object to get them resolved.
Here's how:

'----- start of "air code" -----

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("qryChangeLinks_DAT_Arisings_Remarks")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute dbFailOnError

Set qdf = Nothing
Set db = Nothing
'----- end of "air code" -----

 

[Rob Parker]

Thanks Dirk,

Works beautifully. The only other thing I needed to change was
db.RecordsAffected to qdf.RecordsAffected to show the outcome (Note: I
hadn't included that code in my previous postings, since it wasn't
part of the problem).

Rob