Re: UPDATE query in Access 2003 raising error

Hi Doug,

Elsewhere in the code I use the same technique to execute an SQL statement
(1) build the SQL query and (2) execute it..

I thought that a recordset had to be open to run an SQL command against it.

The detailed code used to execute is below - effectively just an execute. Is
the opening the recordset the problem?

modCommonProcedures.ExecuteQueryDef qdfTemp <== this is a common procedure
as described below:

Public Sub ExecuteQueryDef(qdfName As QueryDef, Optional FailOnError As
String)

Dim errLoop As Error

' Run the specified QueryDef object. Trap for errors

On Error GoTo Err_Execute

If FailOnError <> "Yes" Then
qdfName.Execute dbFailOnError
Else
qdfName.Execute
End If

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

End Sub



"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:ulwS7Xn3HHA.2064@xxxxxxxxxxxxxxxxxxxxxxx
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: creating and executing queries in VBA.
    ... Execute it into what? ... You can't just "execute" a select query. ... When you execute sql it has to go into a report, or a form or a record set. ...
    (microsoft.public.access.modulesdaovba)
  • Re: UPDATE query in Access 2003 raising error
    ... The only reason I questioned the recordset is that you could conceivably ... the SQL looks fine to me. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL statement ...
    (microsoft.public.access.formscoding)
  • Re: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSE
    ... As for the dynamic sql, I am a beginner who is learning by reading the code ... I would create my query in Access and save it as qryAddProj. ... This statement causes ADO to create a recordset to receive the results ... You should use the ExecuteOptions argument of the Execute ...
    (microsoft.public.data.ado)
  • 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)
  • 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)