Read-Only Select Query

[wellsedj]

I have a select query that is based on six tables. The table relationship is as follows: tblSecurities one to many to tblYears, tblYears has a one to many to tblJan1NAV, tblCapGains, tblDividends, and tblDec31NAV. I have removed referential integrity between all of these tables in the Relationships window. Using my select query, I cannot delete any record from tblYears when a related record is in tblJan1NAV, tblCapGains, tblDividends, or tblDec31NAV. I get a message at the bottom of the screen saying "Record not deleted. Data is read-only."

Is it possible for a select query to be read-only?

 

[Rich]

Removing the enforce ref integrity is I think akin to playing Russian Roulette with 5 bullets in the gun, I wouldn't do it if I where you.
Use a delete query to delete records or a form where you can handle accidental deletions.

 

[wellsedj]

Thank you for your quick response. I am new with Access and had never come across a Read-Only select query. I only removed the referential integrity to see if that would change the query so it wasn't read-only.

 

[Pat Hartman]

This is going to be over your head but, any query that contains multiple 1-to-many relationships that are not hierarchial, will be non-updateable. A sample hierarchial relationship would be Customer-->Order-->OrderDetails. One customer-->many orders. One order-->many order details. A non-hierarchial example would be Students-->Parents and Students-->Teachers. Although students has a 1-to-many relationship with parents and students also has a 1-to-many relationship with teachers. Parents and Teachers have no relationship to each other. Therefore a query that tried to return a recordset pulling data from both Parents and Teachers at once would NOT be updateable because it would essentially be a cartesian product.

 

[wellsedj]

Pat,

That is exactly what I have. There is no relationship between tables tblJan1NAV, tblCapGains, tblDividends, and tblDec31NAV. Each of these tables has a hierarchical relationship with tblYears, but not with each other.

Thank you.

Ed