UPDATE query in Access 2003 raising error

Hello...

I am trying to update a record using input from a form in Access 2003.

When I execute the qrydef I get the message:
"Error number 3066 Query must have at least one destination field."

The SQL string that is Debug.PrintED in code below is:
UPDATE tblDesign SET tblDesign.DesignMonarchSide = 'L',
tblDesign.DesignMonarchFacing = 'L' WHERE tblDesign.DesignName = '2VEENS
JVBIEEE' ;

This looks perfectly correct and when copied into the QBF as SQL and then go
into the design side to execute it, it works perfectly but I cannot get it
to work in VBA. I have tried both double (") and single (') quotes.

Am I missing something obvious?
============================================================
Extract from code below:

strDMS = Mid(Me.DMS0101, 1, 1)
strDMF = Mid(Me.DMS0101, 2, 1)

strSQL = "UPDATE tblDesign " & _
"SET tblDesign.DesignMonarchSide = '" & strDMS & "', " & _
"tblDesign.DesignMonarchFacing = '" & strDMF & "' " & _
"WHERE tblDesign.DesignName = '" & Me.DesignName & "' ;"

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblDesign")
Set qdfTemp = db.CreateQueryDef("", strSQLx)
===============================================================
I then execute the query.


.

Relevant Pages

  • Re: Finally which ORM tool?
    ... manipulate the linq query IF you're executing it at that moment. ... simply because the declaration construction was with 'CHOPS'. ... implement IEnumerablebut had an Execute() method which gave back ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • Re: Clarification on DBI module
    ... You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place. ... The sql query can have bind variables or they may not have. ... Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)
  • RE: Running action function from SELECT query
    ... Yes ADO can execute queries saved in an Access database. ... I do not know how ADO.NET works, but the developer tells me he is able to ... At any rate, I can call just a function from a query, like this: ...
    (microsoft.public.access.modulesdaovba)
  • Re: UPDATE query in Access 2003 raising error
    ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
Loading