Solved query based on a snapshot query retrieve the ability to modify data !

[frero]

Hello every one

I have an Access application with separated data. I want to explain to some users how to create their own selection queries. To prevent users from directly accessing the data, I created a dedicated front-end that contains snapshot selection queries on all tables. But I was surprised to see that a query based on these snapshot queries still has the ability to modify, delete, etc.

Does anyone have an idea on how to ensure that the final queries do not inherit these write permissions?

thanks for your help

 

[Gasman]

Why not see what makes a query read only, as see if you can use one of those issues?

 

[theDBguy]

Hello every one

I have an Access application with separated data. I want to explain to some users how to create their own selection queries. To prevent users from directly accessing the data, I created a dedicated front-end that contains snapshot selection queries on all tables. But I was surprised to see that a query based on these snapshot queries still has the ability to modify, delete, etc.

Does anyone have an idea on how to ensure that the final queries do not inherit these write permissions?

thanks for your help

I tried to duplicate your issue but couldn't quite get there. Can you please provide a step-by-step instruction on how to verify the issue you're seeing?

 

[Gasman]

Hmm, I can confirm that I also cannot modify the data in a query that is snapshot?
I just took the O/P's statement to be true.

 

[theDBguy]

Hmm, I can confirm that I also cannot modify the data in a query that is snapshot?
I just took the O/P's statement to be true.

I was trying to link to a snapshot query from a separate FE but couldn't see them as available for linking.

 

[Gasman]

I just changed one of mine to snapshot.

 

[theDBguy]

I just changed one of mine to snapshot.

Guess we'll have to wait for some clarifications from the OP. They mentioned something about using a "dedicated front-end."

 

[Gasman]

I have discovered something a little wierd though.

I created a query as snapshot. That will not allow data to be amended.
I then created a query with the readonly query as it's source. Left second query as Dynaset.

That query will allow amendments that filter through to the table???

 

[The_Doc_Man]

I have discovered something a little wierd though.

I created a query as snapshot. That will not allow data to be amended.
I then created a query with the readonly query as it's source. Left second query as Dynaset.

That query will allow amendments that filter through to the table???

I think that has to be consider a bug. If any query in the layering of a layered query is read-only, to my way of thinking that should be totally read-only all the way from topmost layer to underlying table. One man's opinion.

 

[frero]

Hello everyone and thank you for your replies

I think that has to be consider a bug. If any query in the layering of a layered query is read-only, to my way of thinking that should be totally read-only all the way from topmost layer to underlying table. One man's opinion.

That's what I thought !

To make things clearer, I've attached a photo of the app's structure.
The idea is to let users make their own queries, but without the risk of losing data.
So I was thinking of using intermediate Snapshot requests as a kind of shield.

I've also attached an access file that reproduces the problem. Requete 1 is a snapshot, requete 2 is based on requete 1.
And what I don't understand is that request 2 can modify the data!

best

 

[Gasman]

I think that has to be consider a bug. If any query in the layering of a layered query is read-only, to my way of thinking that should be totally read-only all the way from topmost layer to underlying table. One man's opinion.

Mine to
And I am only on 2019. I still have 2007 on another computer, so will test on that (if I remember) later this week.

 

[xavier.batlle]

I have discovered something a little wierd though.

I created a query as snapshot. That will not allow data to be amended.
I then created a query with the readonly query as it's source. Left second query as Dynaset.

That query will allow amendments that filter through to the table???

FYI, the same behaviour on MS Access 2010 and on MS Access 2003.
I agree with The_Doc_Man, it has to be considered a bug!

 

[ebs17]

I want to explain to some users how to create their own selection queries

Even many developers who ask here have problems creating sensible queries themselves. Now users are supposed to do this as well?
Are users more industrious or smarter than you as a developer? That would be a great disgrace.

Normally, you offer forms that show data. A form recordset can also be made write-protected, but this is simply not provided for queries in Jet/Access. For more variable queries, you offer prepared and easily adjustable filter options.

 

[frero]

Even many developers who ask here have problems creating sensible queries themselves. Now users are supposed to do this as well?
Are users more industrious or smarter than you as a developer? That would be a great disgrace.

Look at Gasman’s signature, it says it all.
Users must respond to ministerial surveys, with requests that change daily. I think it's more efficient to teach them basics of queries, than locked solutions. And with access, make (basic) queries is not so difficult thanks to GUI.

FYI, the same behaviour on MS Access 2010 and on MS Access 2003.
I agree with The_Doc_Man, it has to be considered a bug!

If anyone has another solution, I’m interested.

 

[Gasman]

Well I would perhaps suggest copying the data to another DB which you make Read Only?
If you did not, then a user could change some data, that would be reflected on another report?
I suppose it depends on how old the data is allowed to be, a week, a month, a day?

I worked for one company, where they generated reports before all the data was in, as corrections always occurred.
That then generated no end of enquiries from the respective partys.

 

[isladogs]

Although I understand why several people have written that this is a bug, I'm not sure I completely agree.

The underlying table for each query is editable so the fact that the second query is based on a snapshot query is I would argue irrelevant. You are just using a view of the table and the record source is still the table.

However, if you make the actual query read only e.g. by using SELECT DISTINCT (no need for snapshot), any query based on that is also read only.
That is because you have changed the structure of the record source rather than just how it is viewed.

You can also confirm the difference by creating queries based on read only system tables such as MSysQueries. Those remain read only

 

[GPGeorge]

Allen Browne "Why is my query Read-Only"

 

[frero]

isladogs, you're my hero
and thanks to GPGeorge for this additional information.

I add "distinct" and it works as I expect it to.
I admit that when I use ‘distinct’, I didn't pay attention to the readOnly transition.

I understand your explanation about the snapshot, even though I don’t find this behavior logical.
In my opinion, it should behave like inheritance.

Thank you all for this productive discussion.

 

[isladogs]

You're welcome.