Re: UPDATE query in Access 2003 raising error

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: [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: ADODB.Recordset: Operation is not allowed when the object is closed
    ... > ' Create the ADO Connection and Recordset objects. ... > ' Set the connection string, open the connection and execute the ... If your sql string is an insert/update/delete statement, ...
    (microsoft.public.scripting.vbscript)
  • Re: insert Q
    ... many sql statements on the page). ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ...
    (microsoft.public.inetserver.asp.db)
  • Re: insert Q
    ... >> conn.execute SQL ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ...
    (microsoft.public.inetserver.asp.db)
  • 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)