Dreaded 3073 Non-updatable Query Problem

Sep 28, 2006

I recently inherited an MS Access application. To bring it up to company standards, the data needed to be rehosted in DB2. The screens remain in MS Access. To accomplish this with no/minimal code change, I followed these steps:

1) For each access table, I created a DB2 table with the same structure. (table name and field names changed based on company standard)
2) Created a link table in access for each DB2 table
3) removed each Access table and replaced with a query (based on link table) with same name. The query maps field names us AS clause.

The reports and screens paint fine but any update operation results in a 3073 error. 'Operation must use an updateable query'. There are no joins or aggregates in the query.

Any suggetions?

thanks
Howard

View Replies


ADVERTISEMENT

Linking A Non Updatable Query To An Updatable Table

Jan 17, 2006

I have a query which performs a calculation calculating totals of many fields linked by BikeID This is not updatable due to the calculated field. Now i want to join this query to the BikeDetails table to create a form. So this means that i would have all the data from the table which i can update and the calculated field which i cant. But when i join the two tables/query all the data becomes non updatable.

Thanks
Sci

View 4 Replies View Related

Displaying Linked Updatable And Non Updatable Records In Form

Jan 20, 2006

My problem has now moved on to a form issue which means no one has been able to answer is in the queries section of the forum as there is no way around it using queries so i hope you can help. All information is available on this thread. Thanks, Sci http://www.access-programmers.co.uk/forums/showthread.php?t=100376

View 3 Replies View Related

Updatable/non Updatable Data In Datasheet

Jan 28, 2006

Ok ive been having this sort of problem for a while. Ive been trying to display updatable and non updatable data in a datasheet form. The problem is that i couldnt update any of the details with a calculated field in the table. Therefore i have put the calculated field in a subform thanks to someone on the forums request(sorry i cant remember who it was). After referencing the subforms text box i can display the correct information in form view however i cannot display in in datasheet view without expanding the subform [+] for the form to do the calculation. I need the form to go through all records once it opens unless anyone has a better solution. Hope sumone can help,
Thanks Sci

View 1 Replies View Related

Query Not Updatable

Feb 13, 2006

I have the following simple query. The query joins the data in two tables and I use it in a bound form. This has worked fine for a couple of years, but today the query is saying it is a non-updatable recordset. This stops users changing any data in the form. I have tried the following to no avail:

compact/repair
recreate the query
copy the whole database into a different MDB file

Is there something silly I might be missing??? I did not changed any of the table structure this problem has just appeared.

Code:SELECT tblWorksOrders.*, tblLocations.* FROM tblWorksOrders INNER JOIN tblLocations ON tblWorksOrders.WOLocationId = tblLocations.LCNIdentifierORDER BY tblWorksOrders.WorksOrderNo, tblWorksOrders.InstructionNo;

View 1 Replies View Related

Operations Must Use Updatable Query?

May 23, 2005

Please help, this error is wreking my head:
I have a VB APP updating a an Access DB, i get this error every so ofter:

-214 (load of numbers) Operation must use an updatable query.

I have given full control to to the directory on which the App and the Access sit..
any ideas?

View 3 Replies View Related

Updatable Query Error

Jul 12, 2006

I have a DB where I run two queries every week that extracts data from a Datawarehouse and appends records to a table. Then i run an update query that updates a policy status on the same table the appended records are added to.
For some reason this week when i try to run the update queary i get the following message:

Operation must use an updatable query.

Anyone familiar with this error? The table in not read-only and no other users are in the db

View 3 Replies View Related

Updatable Query Error

Aug 14, 2006

I have the following update query:

UPDATE DISTINCTROW MVADMP_SECURITY INNER JOIN Activity ON MVADMP_SECURITY.CUSIP_NO = Activity.[CUSIP#] SET Activity.[Wire Date] = [BK_ENT_DT]
WHERE (((Activity.[Fannie Mae Loan #]) Is Null));


which works fine in the production environment. However, in the test database, I get an updatabale query error.

The Activity table is a table that is local in each database, while the MVADMP_SECURITY table is in the back end and is used by both the test and production environments. Both Activity tables have the same keys and indexes. I have write permissions for Activity table in both environments, while no one has write permissions to the MVADMP_SECURITY in either environment.

I have tried both copying and importing it into the test environment and the result is always the same; updatabale query error. Does anyone have any idea what's going on?

View 1 Replies View Related

Non-Updatable Query Due To Sums

Aug 14, 2007

I'm struggling with a non-updatable query issue. I have a table at the category level that should allow entry but it needs to pull in information from another query. In this 2nd query, its doing group by/counts/sum's from an item level table. From reading about the dynasets and trying to fix it, it looks like this the major problem.

The item level table has information that is changing based on other user input so I don't think using a make table would work. I tried setting the queries/form to dynaset (inconsistant updates) but no luck there. It still wouldn't allow any changes to the entry field. Any clue on how I could get around this?

Main Table
Category Entry
A. . . . ._____
B. . . . ._____

Item Table
Item Category Sales
1. . . . . .A . . . . $10
2. . . . . .B . . . . $15
3. . . . . .B . . . . $10

This query built using the main table joined with a group by/sum of the item table.
Goal Query for Updates
Category Sales Entry
A. . . . . . . $10. ____
B. . . . . . . $25. ____

View 4 Replies View Related

Work Around Aggragated Query To Make Updatable

May 22, 2006

The user currently manual checks the Use Part? box but would I would like to automate with an update query. I have this query that needs to be updatable but has a group by and min to get the correct results. I plan to run this query by another update query to update the table, but its currently not updatable. Is there a workaround or does anyone have another scheme to go at it. Here is my current queries.


SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], Min([t_BOM Part Cross].[Alt Unit 1]) AS [MinOfAlt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
GROUP BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Use Part?]
HAVING ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND ((Min([t_BOM Part Cross].[Alt Unit 1]))>0))
ORDER BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #];


I have another query that I used a SUB SELECT ON THE [Alt Unit 1] but does not group the other fields to get the results but it is updatable.


SELECT [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Alt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
WHERE ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND (([t_BOM Part Cross].[Alt Unit 1])>(SELECT MIN([Alt Unit 1])>0
FROM [t_BOM Part Cross])))
ORDER BY [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #];


thanks for any help available

View 1 Replies View Related

The Ever Dreaded Many To Many

Sep 21, 2007

I can't quite get my head wrapped around this, so any help will be appreciated. Here's my set up.

Every table has an AutoNumber Unique ID to make things simple.
This is about insurance information for a company that owns a lot of property.
There are other tables in my database that I am not mentioning because they do not affect my many to many drama. So if it looks incomplete, it (hopefully) isn't and I just didn't mention it.


Tables are:

tblBuilding
BuildingID
BuildingName


tblPolicy
PolicyID
EffectiveDate
ExpirationDate

tblPolicyCoverage
PolicyCoverageID
CoverageType
PolicyID

tblCoverageInfo
CoverageInfoID
PolicyCoverageID


True Statements about this information:

A "Policy" has many "Policy Coverages"
A "Policy Coverage" has many "Coverage Info"
A "Building" has many "Policy"
A "Policy" has many "Building"

So my Building/Policy relationship is the many to many relationship. So I want to create a middle table which will have a New ID, and the Building ID and the Policy ID. I think.

Here is my confusion. Do I create a middle table to connect the building to the policy? Or do I connect the building to the policy coverages? or do I connect the building to the Coverage Info? Because it's true that a building has many policies, many coverages and many coverage info. I'm thinking I put it at the policy level but I'm not 100% on that.

I fear I won't get this answer without hours of trial and error and I don't have hours being this is just a side thing for one client because they own like 200 properties. If you need more info, let me know. Any suggestions, much appreciated. Thank you.

View 4 Replies View Related

Possible To Create An Updatable Query Listing Description Of Tables And Fields?

Aug 16, 2007

As I usually concentrate on building the tables and fields in my initial stage, and do some experimenting to analyze the relationship.

However, when I'm done, I find it quite a hassle to go manually through the tables to add description to each field for documenting.

What I would like to do is create a query that will list all fields and its description. Now, I have found codes to retrieve either table or fields properties, including the description property, but am kind of stumped on how I can make it a SQL statement so I can create a temporary query, which will be pretty be one time thing, that can read all fields' description and allow me to type in it, save it.

If that's not possible, I suppose I could write code, using Allen Browne's example, that would loop through the fields' description, debugging to the immediate window and prompting me if I would like to add something to the blank description. However, this isn't exactly greatest as I need to see all fields so I can be sure I'm giving good descriptions.

I figured someone may have had this same problem and maybe came up with a solution?

View 4 Replies View Related

Creating An Updatable Query Requiring Only One Record From Many Side Table

Sep 15, 2006

I have a form that stores information about attendees for a given class. In this particular instance, I only store one address, one contact information both which are optional. I've used subforms, but that has bought problems (one notable problem is that one field from tblAddress is required for *any* attendee, which is county they resides in, whether they give out an address or not. At this point my VBA codes to work around this is simply too buggy and a major hassle.

Therefore, I am now considering two possibilities: 1) make a unbound form and do everything manually without any subforms or 2) create an updatable query somehow that can pull together the needed information. The latter, I'm not sure if that is feasible, so I'm asking you about that.

Address and Contact Information are kept as a many side tables, and different queries I've made in past only is either non updatable or partially updatable, which does no good. If anyone can show how one can retrieve only one record from the many side and keep it as updatable, that'd be great.

TIA.

View 5 Replies View Related

The Dreaded Stock Control Database!

Dec 14, 2006

I have started making a stock control database, i have spent about a week on it, And i am struggling! There are obviously lots of different ways to actually build them efficiently. My problem is that i want to add stock and take stock out, changing the stock quantity and obviously keeping track of stock. I only want to ammend the stock quantity, in the stock table if the item is not a new item. The stock is added via a log sheet which could also have a new item. My head is beginning to hurt, but with a bit of help from you guys i might be able to crack it.

The Tables I have are:

tblStock

tblCategories

tblSuppliers

tblInventory transaction

tblStock_in

tblStock_Out

tblEmployees

Is this sufficient? Can i add stock and take stock out with one transaction table? Or would i need to add another table? I believe the code would be through a query but again am a little unsure as never done anything like this!

Please help!!

View 8 Replies View Related

"Operation Must Use An Updatable Query." Error

Oct 3, 2007

I tried to update a column in a table (tblReservedPart) from a query (qryF4101) using Update Query. Here is the code:

UPDATE tblReservedPart INNER JOIN qryF4101 ON [tblReservedPart].[PN]=[qryF4101].[PartCode] SET tblReservedPart.strProductLine = [qryF4101].[IMGLPT]
WHERE [tblReservedPart].[PN]=[qryF4101].[PartCode];

I received an error "Operation must use an updatable query." I am not updating the query. What is the error complaining?

Thanks.
DanYeung

View 1 Replies View Related

The Dreaded "You Can't Assign A Value To This Object" Error Message

May 13, 2005

Hi Gang,
I'm new to this forum so please bare with me.
I have spent the last two days trying to resolve, or work out why on earth I am getting this error message.
I am at the point of pulling my hair out - its SO frustrating so *any* suggestion would be great.
I have created a small database for plant (as in flowers) details. To cut a long story short, its a simple db fronted by a
form that allows me to display and add records together with an associated image (stored outside the db).
I was trying to write some code that would look at the control that displays the image and, if there is no image associated
with a particular record, it would display an "add new image" button. If there is an associated image, the buttons visable property is set to false.
I had ammended a section of code for the forms "on current" event and saved the changes. I then changed to form view, tried to enter a new record and access throws up the error message. It does not matter which field I try to add data in, the same error still happens. I have checked, and re-checked all my code and property settings for every control and field on the form and I can not track down what is causing this error.
I noticed from searching this forum that someone else had this error but the resolution does not appear to have been posted so can anyone help in any way at all.
My grateful thanks,
Kenny

View 10 Replies View Related

The Dreaded "Vacation Accrual" Queston.

Jun 5, 2006

Please bear with me.

I'm fairly new to access, and have learned much in the past three weeks I've been "learning" how to use it.

I was simply sick of the process that was going on at my place of business where I'm a Manager (I've been there for about 3 months). We keep about 10 Excel forms for the different pieces of information (production numbers and such) we keep for each individual in the department. There are 115 people, 3 shifts working 24 hours a day. Each morning I would go in and open each excel file to analyze what happened the day before.

We're also tracking an individual's days off, tardiness, quality... you name it.

I finally decided it was time for a database... I had worked with them in the past, but I had never actually "built" one. I've actually knocked out a significant portion of what needs to be done, and I'm proud of my product thus far... except for one thing:

Vacation accrual is "kicking my posterior"!

I've searched these forums (finding some answers, most of which I don't understand), but I almost need to be handed the answers. My other struggles I've overcome with the help of these very forums and Google.

I'll lay out our policy and what I need and hopefully someone can help me. As I learn access (and I will!), maybe one day I could come back here and help someone else that may be in need.

Our policy is as follows:

90 Days to be eligible for accrual.

1-3 years of service - 6.67 Hours a Month (10 Day a year or 80 Work Hours... all the same, and whichever makes things easier)
4-7 years of service 10 hours a month (same deal as above as 15 days or 120 work hours)
+8 years -13.33 hours a month (blah blah)

The hours do NOT carry over, they are all use it or lose it. I can make manual entries when necessary, so it doesn't have to be all "automatic".

Like I said, the searching I've done has come up with some things, but not in the language I yet understand. I do know "modules" and some expressions, but the more that could be "spelled out" the better.

I've got your (what I consider) normal Employee fields, and would like to use something like "DateStarted" as a beginning point, but I'm open to anything.

Thank you VERY much in advance for any help that can be given. This has stalled my project almost a week trying to figure out a solution!

View 10 Replies View Related

Recordset Not Updatable

Nov 11, 2005

krispi (MIS) 11 Nov 05 9:36
Hi all

I have a database of mortgage applications.

I have a query 'QryCasesToWork' which identifies applications to work based on a complex set of criteria.

Once the query has run, I need a field called 'ToDo' to update to 'yes'. Unfortunately, one of the preceding queries which goes to make up QryCasesToWork is a Union query, meaning that all the subsequent results are non-updatable.

Is there an easy way to take the application numbers returned in QryCasesToWork, and update the appropriate ToDo fields in the table?

I had a vague thought of using a Make Table query form the results of QryCasesToWork then querying that table against my main table to update the field, but this seems to me to be a cumbersome and unwieldy solution.

Does anyone have the 'correct' solution? Any help greatly appreciated.

Thanks
Chris

View 3 Replies View Related

Updatable Queries Many To One

Apr 29, 2006

Perplexed as usual.

I have a query that is made from 2 tables in a one to many relationship
The many side is a sum query based on the foreign key from the main table

An example of this would be TblOrders and TblOrderDetails, where the group for the sum of total cost would be the OrderID number from the TblOrderDetails table, which is the foreign key.

Why can’t any fields from the TblOrders be edited in this query?

There can be one and only one record that satisfies that join.

I’m so confused.

View 6 Replies View Related

[B][/B]recordset Not Updatable, Please Help

Oct 13, 2006

Hi!

A form is based on query which is based on a table.

The table is splitted into 2, then the form has a "recordset not updatable" error...

does anyone have any idea on this?

thanks very much

View 1 Replies View Related

Recordset Is Not Updatable

Dec 3, 2004

I am trying to update a table in our join database (Access), but it gives me error "The Recordset is not updatable."
This table is a share table. I believe somebody in my group try to set security setting level or try to join several tables. I'd appreciated any help.

Cheer
n

View 6 Replies View Related

Updatable Active X Calander

Mar 9, 2005

Is there a calander that I can put on my form that will update to current date when I open form

Gary

View 2 Replies View Related

Record Not Updatable Error Message - Help!!

Nov 16, 2005

:eek:

I have a query in Access which I have always updated no problem until today. I tried to update the usual fields and it says "This recordset is not updatable". Does anyone know why this would have changed and how I can get it back to updatable?

many thanks

FJ

View 2 Replies View Related

Recordset Is Not Updatable! Field Just Beeps

Feb 24, 2006

Hi all,
Not sure where to start on this one!
First I have a main form “FormA” formA has a subform “FormB” in a datasheet format. I have one of the fields in “FormB” Double-Click Event procedure as follows:

Private Sub Results1_DblClick(Cancel As Integer)
Me.Refresh
Dim stDocName As String
Dim stLinkCriteria As String

If panel = 5 Then
stDocName = "Subform5"
ElseIf panel = 8 Then
stDocName = "Subform8"
ElseIf panel = "DOT" Then
stDocName = "Subform5"
ElseIf panel = "Breath" Then
stDocName = "SubformBA"
ElseIf panel = "Hair" Then
stDocName = "Subform5"
Else
stDocName = "Subform10"
End If

stLinkCriteria = "[DSID]= " & Me![DSID]

DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

This then pops-up a “FormC” (Subform5, Subform8, Subform10, or SubformBA) linked to a specific record from “FormB”. FormC has a drop down field that will allow you to select “Negative or Positive”, and then when FormC is closed, FormB is updated.

This has been working fine for the past 4 months. NO PROBLEMS!!!!

NOW, for no apparent reason when FormC pops-up and I try to click the drop down field; it just BEEPS at me (Almost like the field has locked the user out), then when I close FormC, I get and error: “Recordset is not updatable”. On the FormC (SubformBA), I get no error on close, but I get the word “#error” in the field on FormB.

Has this happen to anyone? Can someone help I have exhausted all thoughts.
Thanks-a-million, ENVIVA

View 2 Replies View Related

Forms :: Updatable Text Field On Form?

Jun 19, 2015

I have a form (frm_riskcalc3) which its data source is a table called: tbl_targets, on the 'frm_riskcalc3' form are; 15 unbound label text Fields and 15 unbound number value fields that are pre-set with data, I need to find a way to allow these fields to be updated without changing what their values were before.

[URL]

View 1 Replies View Related

Forms :: Make Fields Selectively Updatable

Nov 6, 2014

I have a form consisting of a list box and some fields belonging to a single record. When the user highlights a row in the list box then the fields show the details of that record. Now the whole process is Read only. But I wish to put a toggle switch on the form so that the detail fields of the record become updateable. I have tried the following constructs but I could not make those fields updateable.

Code:
Me.AllowEdits = True
Me.DataEntry = True
Me.fld1.Enabled = True
Me.fld2.Locked = True

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved