473,856 Members | 1,724 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Operation must use an updateable query..

94 New Member
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_o ccurrences uio
SET fes_active_plac es =
(SELECT COUNT(*) FROM registration_un its ru
WHERE ru.fes_unit_ins tance_code = uio.fes_uins_in stance_code
AND ru.uio_occurren ce_code = uio.calocc_occu rrence_code
AND ru.progress_sta tus = 'A'
AND ru.uio_occurren ce_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.Query Defs("Active Update Query").SQL =
"UPDATE fes_unit_instan ce_occurrences uio " & _
"SET fes_active_plac es = " & _
"(SELECT COUNT(*) FROM fes_registratio n_units ru " & _
"WHERE ru.fes_unit_ins tance_code = uio.fes_uins_in stance_code " & _
"AND ru.uio_occurren ce_code = uio.calocc_occu rrence_code " & _
"AND ru.progress_sta tus = 'A' " & _
"AND ru.uio_occurren ce_code = " & Me!cboYear & ");"
Set qdfPassThru = db.QueryDefs("A ctive Update Query")
qdfPassThru.Exe cute
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 3667
Scott Price
1,384 Recognized Expert Top Contributor
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 New Member
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 Recognized Expert Top Contributor
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
902
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 in question: myDataRow = myDataSet.Tables(0).Rows(RowNum) myDataRow(ColumnCount) = Ailment Adapter.UpdateCommand = New OleDbCommand("UPDATE SET F" & ColumnCount & " = '" & Ailment & "' where F1 = " & RowNum & "", Conn)...
6
7575
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 must be an updateable query" messages when I try to run the same old queries. Here is a sample of one that is no longer working: UPDATE Personnel INNER JOIN dbo_personnel ON .= dbo_personnel. SET personnel. = dbo_personnel.,...
4
6508
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 = New OleDbCommand("INSERT INTO LOGIN_MASTER (LOGIN_ID, PWD, F_NAME, L_NAME, TYPE_ID) VALUES ('" & strEmail & "','" & strPwd & "','" &
606
14822
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 out INSERT statements. Here is the error which I am getting: Exception Details: System.Data.OleDb.OleDbException: Operation must use an updateable query.
2
7517
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 Updateable Query' error. Points: - The database is in my local temp directory, not inetpub - The directory has read/write permissions - The database has read/write permissions - I created another version of the dbase and tried to reference it...
8
3012
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: ======================================================== ======================================================== Server Error in '/' Application. -------------------------------------------------------------------------------- Operation must use an updateable query. Description: An unhandled exception occurred during...
1
3587
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 something back to the excel worksheet. My problem is that i'm having this annoying exception: "Operation must
11
3975
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 generates the following error: Operation must use an updateable query. pointing to a line that says
1
2760
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 whenever i am trying to run this application then it through the exception. I have used MS Access as a database. The error is as follows... Server Error in '/ASG' Application....
0
2479
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 name or workbook name, but the logic is the same. This method is preferable to me because it's efficient and there's much less chance for user error. But when I use the query I get the dreaded Error 3073, "Operation must use an updateable query". ...
0
9758
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10693
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10773
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10378
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7929
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5956
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4571
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4170
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3196
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.