UPDATE pass-through query from code fails with error 3066

Question

"3066- Query must have at least one destination field." is the result of the following command:

DoCmd.OpenQuery "PriceUpdateQuery"

However, this is an UPDATE query, not a select query, and as such, THEY DO NOT HAVE OUTPUT FIELDS. 

I know there is a property that can be set, "Returns Records", and I have that property set to "no."

This does not seem to be a totally consistent problem, occasionally it lets one through.

How can I make this work?

Answer 1

Use the Execute method instead of OpenQuery:

CurrentDb.Execute "PriceUpdateQuery", dbFailOnError

with appropriate error trapping.

---

John W. Vinson/MVP

Answer 2

That works perfectly.  Thank you.

Answer 3

As I have run UPDATE queries like this on rare occassions I see no reason why you are getting this message.  So it would help if you would post the SQL of the query to see if there is something else.

---

--

Gina Whipp

2010 Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Answer 4

Hi!!!!

Please post your sql script, so we can help you

or you can do what I do:

'Update my table via my function...

var_sql = "UPDATE my_table SET my_field = 'Another Option'"

my_conn (var_sql)

Public Function my_conn(var_sql As String)

On Error GoTo Err_my_conn

Dim cn As ADODB.Connection

Dim cmd As ADODB.Command

  Set cn = New ADODB.Connection

  Set cmd = New ADODB.Command

  cn.ConnectionString = "DSN=MY_ODBC_CONNECTION"

  cn.Open

  Set cmd.ActiveConnection = cn

  cmd.CommandText = var_sql

  cmd.Execute

  cn.Close

  Set cmd = Nothing

  Set cn = Nothing

Exit_my_conn:

    Exit Function

Err_my_conn:

    MsgBox Err.Description

    Resume Exit_my_conn

End Function

:)

---

I hope this helps, Bre-x just a regular user, not an expert