Avatar of etech0
etech0
Flag for United States of America asked on

Access query - "This recordset is not updateable"

Hi!
I have a simple Access query that takes all the fields from one table, and also uses another table with a corresponding field to set a criteria.
The query is the recordsource of a form, and in the form if I try to edit anything I get a message - "This recordset is not updateable".
How can I fix this?

In case it helps, here is my query:
SELECT CartonDimensionsT.*, NewProdT.division
FROM NewProdT RIGHT JOIN CartonDimensionsT ON NewProdT.NewProductBatchID = CartonDimensionsT.BatchID
WHERE (((NewProdT.division)=True));
Microsoft Access

Avatar of undefined
Last Comment
trbaze

8/22/2022 - Mon
etech0

ASKER
I managed to simplify my query a bit, but it's still not editable!!

SELECT CartonDimensionsT.*, NewProdT.division
FROM NewProdT INNER JOIN CartonDimensionsT ON NewProdT.model_no = CartonDimensionsT.ICITEM;
Jim Dettman (EE MVE)

1. Make sure the primary key of the table your trying to edit is in the resultset.
2. You may have to use SELECT DISTINCT

Jim.
etech0

ASKER
what's resultset?

The table I'm trying to edit actually has 3 primary keys. Is that a problem?

If I would make it 1 primary key, it wouldn't be a field that's in the other table anyways.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Jim Dettman (EE MVE)

<<what's resultset?>>

 The data the query returns.

<<The table I'm trying to edit actually has 3 primary keys. Is that a problem?>>

  A table can't have 3 primary keys.  It may have 3 different keys that are all unqiue, but only one can be choosen as the primary key.  Look in the table design and look for the little key in the left column or click the indexes button and look for the index named "Primary Key" for which field(s) in the table should be included.

Jim.
etech0

ASKER
The primary key is in the resultset.

There are three primary keys - see below.
screenshot
etech0

ASKER
I tried adding an autonumber, and setting that to be the primary key, but it didn't solve the problem.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

<<The primary key is in the resultset.

There are three primary keys - see below.>>

OK, now I know what you meant; what you have there is three fields that make up the primary key.

<<I tried adding an autonumber, and setting that to be the primary key, but it didn't solve the problem. >>

  Adding the autonumber was a good idea and you should leave it.  The problem with a natural key (what you were using is that the key length can become long.  That becomes inefficent when you start saving the key in other tables.

So leave the autonumber and leave it set as the primary key.  You should also leave an index on the three fields that you had as a primary key and mark the index as unique.

Now back to the updatable problem.  Access marks a recordset as not updatable when in the result set, it cannot determine which row in the underlying table needs to be updated.

Since adding the unique key to the output did not do the trick, try making the SQL:

SELECT DISTINCT CartonDimensionsT.*, NewProdT.division
FROM NewProdT INNER JOIN CartonDimensionsT ON NewProdT.model_no = CartonDimensionsT.ICITEM;

 Also, rather then the wildcard for CartonDimensionsT, bring down the fields you need specifically (even if it's all of them).

Jim.
etech0

ASKER
I followed the above instructions, and it still doesn't work.

Here is my sql:
SELECT DISTINCT CartonDimensionsT.ID, CartonDimensionsT.ICITEM, CartonDimensionsT.vendor, CartonDimensionsT.BoxNo, CartonDimensionsT.BoxL, CartonDimensionsT.BoxW, CartonDimensionsT.BoxH, CartonDimensionsT.BoxWt, NewProdT.division
FROM NewProdT INNER JOIN CartonDimensionsT ON NewProdT.model_no = CartonDimensionsT.ICITEM;

Could it be a problem that neither of the 'linked' fields are primary keys?
SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
etech0

ASKER
is equi join choice 1? (it's not labeled like that, so I'm just double checking.)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
etech0

ASKER
I tried bring in both primary keys, and it is still not editable.
nito8300

Hi, try this:
1) Click on File
 
2) Click on then Options
 
3) In the resulting window, Select 'Trust Center" on the left
 
4) Click the "Trust Center Settings" button.
 
5) Select "Trusted Locations" on the left side.
 
6) Use the "Add new location" button to navigate to the folder where your file exists.  If you select a parent folder for many files, MAKE SURE you also check the "Subfolders of this location are also trusted".
 

One important final clue, if you are working with files on the network, make sure you select "Allow Trusted Locations on my network" in the trust center


from: http://social.technet.microsoft.com/Forums/en-US/officeappcompat/thread/7f3f1586-8934-423c-8b29-f19159b49761
Jim Dettman (EE MVE)

<<is equi join choice 1? (it's not labeled like that, so I'm just double checking.) >>

 Yes.  You'll get no errors on the join line.

Jim.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
trbaze

Perhaps it has something to do with the table relationships.  Either the relationship between CartonDimensionsT and NewProdT needs to be set to a cascade update or a field from one of those tables is dependent on a join field existing in another table.
etech0

ASKER
@nito8300: The document is trusted. Is that enough?

@trbaze: What do you mean by 'join field'?
trbaze

By join field I mean that perhaps a field from either CartonDimensions or NewProdT has a referencial integrity relationship ( or join) with another table that is preventing you from saving the record.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
etech0

ASKER
Both tables have relationships with other tables, but none have referential integrity. In fact, it is grayed out for all those relationships.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
etech0

ASKER
No.
If I try to create one, I get an error. There is a relationship between them, but not an integrity one.
etech0

ASKER
Just realized that the relationship was referring to the wrong field. I fixed it, and all is well.

Thanks for all your help and hard work!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
trbaze

Glad you got it working.