By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,258 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Operation must use an updateable query..

P: 94

I am trying to update a table which is held in a SQL database, from Access.

I have written the following SQL statement which runs perfectly as either a PTQ in Access or even run from SQL Tools 1.4.2.
UPDATE unit_instance_occurrences uio
SET fes_active_places =
(SELECT COUNT(*) FROM registration_units ru
WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code
AND ru.uio_occurrence_code = uio.calocc_occurrence_code
AND ru.progress_status = 'A'
AND ru.uio_occurrence_code = 07);
The problem only arrises when trying to execute the same code in VB from a button on an Access form. (This is necessary because the year (07) is selected by the user on the form)

My VB is......
Private Sub New_Click()
Dim db As DAO.Database
Dim qdfPassThru As DAO.QueryDef
Set db = CurrentDb
CurrentDb.QueryDefs("Active Update Query").SQL =
"UPDATE fes_unit_instance_occurrences uio " & _
"SET fes_active_places = " & _
"(SELECT COUNT(*) FROM fes_registration_units ru " & _
"WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code " & _
"AND ru.uio_occurrence_code = uio.calocc_occurrence_code " & _
"AND ru.progress_status = 'A' " & _
"AND ru.uio_occurrence_code = " & Me!cboYear & ");"
Set qdfPassThru = db.QueryDefs("Active Update Query")
Set qdfPassThru = Nothing
Set db = Nothing
End Sub

I am repeatedly seeing the error message "Operation must use an updateable query" and i don't understand why. It can't be a permissions error because i clearly have sufficient access as the code runs sucessfully as a standalone PTQ.

Any help will be greatly appreciated. Thanks in advance.
Sep 19 '07 #1
Share this Question
Share on Google+
3 Replies

Scott Price
Expert 100+
P: 1,384
What value is being returned by Me!cboYear ?? What is the RowSource of this combo box, and which is the bound column? Does it, in fact, reside on the form from which you are running this query?

Sep 21 '07 #2

P: 94
At present my form only consists of 1 combo box and 1 button. The combo box is picking up a list of years from an ODBC linked table.
Sep 21 '07 #3

Scott Price
Expert 100+
P: 1,384
At present my form only consists of 1 combo box and 1 button. The combo box is picking up a list of years from an ODBC linked table.
You haven't answered my question!

What value is being returned by the combo box? This is indicated by it's row source and it's bound column.

For example: given combo box name cboYear, Rowsource
Expand|Select|Wrap|Line Numbers
  1. Select YearID, Year From tblYear Order By Year
Bound column = 1.

The value returned by the setup I indicated will be an ID number not a year number.

Sep 21 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.