UPDATE query in Access 2003 raising error

[Michael T]

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.

 

[Douglas J. Steele]

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?

 

[Michael T]

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

 

[Michael T]

I have tried not opening the recordset but still get the same error message
suggesting it is the SLQ statement.

All the best,

Michael.

 

[Douglas J. Steele]

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

 

[Michael T]

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]

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 & "' ;"

 

[Michael T]

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]

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