473,320 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Operation must use an updateable query..

94
PLEASE HELP!!

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")
qdfPassThru.Execute
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
3 3003
Scott Price
1,384 Expert 1GB
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?

Regards,
Scott
Sep 21 '07 #2
Lewe22
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
1,384 Expert 1GB
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.

Regards,
Scott
Sep 21 '07 #4

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

Similar topics

8
by: Tom wilson | last post by:
This is driving me nuts. I'm trying to update an Excel spreadsheet using ADO.Net and Oledb in VB.Net. The connection is open, the adapter is connected and the dataset is loaded. Here's the code...
6
by: ano1optimist | last post by:
I have been running these queries in Access 2000 with no problems. This week, I had to install Access 2003 to create some runtime versions for another application, and now I keep getting "operation...
4
by: MDW | last post by:
Hey all. I'm confused. I'm trying to add a single record into an Access 2000 database using ASP.Net. Here is the code: objConn = New OleDbConnection(strConnect) objConn.Open objCommand =...
606
by: Neil Zanella | last post by:
Hello, I am trying to update an MS access database from ASP.NET. I am using IIS on Windows XP Pro. I can issue SELECT statements from ASP.NET using ADO.NET but I cannot seem to be able to carry...
2
by: SheryMich | last post by:
Hi - I am having a bit of a problem with the insert into a database. When I go to insert a record into an un-keyed, single table Access database, I get the aforementioned ''Operation Must Use an...
8
by: Jim in Arizona | last post by:
I've been using an example out of a book to be able to edit the rows in a database. I am getting the following error: ========================================================...
1
by: Muskito | last post by:
HELP!!! Hello All, I'm using VB.net 2003 and trying to update data in Excel worksheet. The program selects data from the excel, updates something in the MSSQL DB and then tries to update...
11
by: Arpan | last post by:
I have always been working with SQL Server 2005 for ASP.NET apps but due to some reasons, had to revert back to MS-Access 2000. When I try to insert/update a MS-Access DB table (MDB), ASP.NET...
1
by: pavya | last post by:
Hi, I have developed one Web application. At that time my system had a FAT file system on it and this application worked properly. But now i have converted FAT file system to NTFS file system and...
0
by: rickmedlin | last post by:
I know this has been posted on elsewhere but I'm stuck. I'm using the following append query to copy an Access query to Excel: INSERT INTO . SELECT * FROM Test; This isn't the real table...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.