473,574 Members | 2,613 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 3024
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...
6
7552
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...
4
6496
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
14455
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...
2
7506
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...
8
2997
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....
1
3574
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
3942
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
2746
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...
0
2466
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. ...
0
7803
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8230
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...
0
6451
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5618
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...
0
5299
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3739
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2240
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
1
1337
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.