Re: UPDATE query in Access 2003 raising error

How are you executing the query? It almost sounds as though you're trying to
open a recordset using it, as opposed to simply using the Execute method
(although you do say "When I execute the qrydef")

What's the recordset rec for, and why are you opening it before you run the
query?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Michael T" <michaelj@xxxxxxxxxxxxx> wrote in message
news:DuednTBhWP95N1zbnZ2dnUVZ8ternZ2d@xxxxxxxxxxxx
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: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT I
    ... This statement causes ADO to create a recordset to receive the results ... of the query being executed and assign that recordset to your qryAddProj ... You should use the ExecuteOptions argument of the Execute ... string containing a sql statement to be executed. ...
    (microsoft.public.data.ado)
  • 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)