Re: UPDATE query in Access 2003 raising error

Hi,

If I run without procedure I get exactly the same result but slightly
different message wording:

Runtime error 3066 Query must have at least one destination field.

I'm stumped! The SQL works fine if I use it in the QBF Design mode.

Just a thought - what would Access mean by a "destination field"?

All the best,

Michael.


"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:%23W9%23UAo3HHA.536@xxxxxxxxxxxxxxxxxxxxxxx
The only reason I questioned the recordset is that you could conceivably
cause contention on the table.

Sorry, the SQL looks fine to me.

What happens if you run the Execute method directly against the query,
rather than calling that common routine?

I.E.

Set qdfTemp = db.CreateQueryDef("", strSQLx)
qdfTemp.Execute dbFailOnError

instead of

Set qdfTemp = db.CreateQueryDef("", strSQLx)
modCommonProcedures.ExecuteQueryDef qdfTemp

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


"Michael T" <michaelj@xxxxxxxxxxxxx> wrote in message
news:pICdnSFfNoOWK1zbnZ2dnUVZ8sCsnZ2d@xxxxxxxxxxxx
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: [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: ADO.NET query execution much slower than SQL Management Studio
    ... A stored procedure uses a query plan that's created when the SP is first executed and reuses that plan regardless of the suitability of the plan from that point forward--until it's replaced. ... Something that would affect the .NET SqlClient but not SQL Mgmt Studio? ... takes less than one second to execute. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: UPDATE query in Access 2003 raising error
    ... Doug Steele, Microsoft Access MVP ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
  • 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)