Re: UPDATE query in Access 2003 raising error

Well at least we can both sleep better tonight! <g>

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


"Michael T" <michaelj@xxxxxxxxxxxxx> wrote in message
news:zsednf_IvKoBS1zbnZ2dnUVZ8qWhnZ2d@xxxxxxxxxxxx
Doug,

I have just found the error! So obvious and embarrasing - I put the SQL
string into a variable strSQL but create the qrydef from a string strSQLx
(I copied the code form another module and missed the difference). It
works perfectly now. Sorry to have wasted your time.

It had to be something silly!

All the best,

Michael.

"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:%23GQTV3o3HHA.5852@xxxxxxxxxxxxxxxxxxxxxxx
In this context, I believe a destination field means a field that's going
to get a value in the Update statement.

I have one last suggestion. Try removing the table name from the fields:

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


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


"Michael T" <michaelj@xxxxxxxxxxxxx> wrote in message
news:KJKdnZcrt_L3UlzbnZ2dnUVZ8s2mnZ2d@xxxxxxxxxxxx
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: Strange result from query, > gives same result as >=
    ... Jerry Whittle, Microsoft Access MVP ... Same type of query works very fine on same type of table in SQL Server. ...
    (microsoft.public.access.queries)
  • 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)
  • 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)