Access query - "This recordset is not updateable"

etech0
etech0 used Ask the Experts™
on
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));
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.

Author

Commented:
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.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<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.

Author

Commented:
The primary key is in the resultset.

There are three primary keys - see below.
screenshot

Author

Commented:
I tried adding an autonumber, and setting that to be the primary key, but it didn't solve the problem.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<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.

Author

Commented:
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?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<Could it be a problem that neither of the 'linked' fields are primary keys? >>

 Yes, that's one of the reasons that a recordset may not be updateable.  That's why I had you bring down the key for the table you were trying to update as I assumed you had a valid reason for not doing so.

 The rules for when and when not a recordset are updateable are many.

 Remove the DISTINCT since that didn't help and for the heck of it, just try making the join an equi join (rather then an outer join) and make sure that the primary keys from both tables are in the result set.

 If it's still not updateable, then it's a result of the join itself as we've covered everything else at this point.

Beyond that, we would need to step back and ask why your attempting to update this result set and what might be another way of accomplishing that.

Jim.

Author

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

Author

Commented:
I tried bring in both primary keys, and it is still not editable.

Commented:
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)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<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.

Commented:
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.

Author

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

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

Commented:
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.

Author

Commented:
Both tables have relationships with other tables, but none have referential integrity. In fact, it is grayed out for all those relationships.
Commented:
Is there an integrity relationship between CartonDimensions and NewProdT?

Author

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

Author

Commented:
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!

Commented:
Glad you got it working.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial