Link to home
Start Free TrialLog in
Avatar of etech0
etech0Flag 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));
Avatar of etech0
etech0
Flag of United States of America image

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;
Avatar of 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.
Avatar of 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.
<<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.
Avatar of etech0

ASKER

The primary key is in the resultset.

There are three primary keys - see below.
User generated image
Avatar of etech0

ASKER

I tried adding an autonumber, and setting that to be the primary key, but it didn't solve the problem.
<<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.
Avatar of 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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of etech0

ASKER

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

ASKER

I tried bring in both primary keys, and it is still not editable.
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
<<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.
Avatar of trbaze
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.
Avatar of etech0

ASKER

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

@trbaze: What do you mean by 'join field'?
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.
Avatar of 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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of etech0

ASKER

No.
If I try to create one, I get an error. There is a relationship between them, but not an integrity one.
Avatar of 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!
Glad you got it working.