Prompting to save a copy of query (1 Viewer)

[2RUEXX]

Hello,

Sometimes when a user makes a layout change to a query and Access prompts the user upon closing the query if changes should be saved, it then prompts the user to save a copy of the query instead of just saving the layout changes to the original query.

I am not sure why at times Access continually prompts the user in this way, and at other times it simple saves the layout changes to the original query without prompting to save a new query.

What causes Access to behave this way at times and how can I avoid this "save a copy" prompt? Thanks

 

[ebs17]

Do your users work on queries themselves? For me as a developer that would be an indictment.

 

[Pat Hartman]

Do NOT allow users to interact with queries directly. Users should interact only with forms and reports. Unless you specifically construct your queries to be not updateable, you cannot prevent users from modifying data if you present them with queries.

 

[GPGeorge]

Hello,

Sometimes when a user makes a layout change to a query and Access prompts the user upon closing the query if changes should be saved, it then prompts the user to save a copy of the query instead of just saving the layout changes to the original query.

I am not sure why at times Access continually prompts the user in this way, and at other times it simple saves the layout changes to the original query without prompting to save a new query.

What causes Access to behave this way at times and how can I avoid this "save a copy" prompt? Thanks

While I do agree with the other responders that allowing users to modify existing queries is a high-risk practice, the actual question as to when Access prompts for a save or not is worth considering. I think it depends on where and how the query is opened for modification. It might be worth pursuing the circumstances which give rise to it. I don't actually know off the top of my head, but I think it might be related to whether you open the query directly from the Navigation Pane, or from the design view of a form or report which uses it as a recordsource. Look into that.

But ultimately, you might want to rethink the way users of this relational database application interact with it.

 

[The_Doc_Man]

I've got a different question. Is this a split database to Front-End/Back-End, or is a single-file (monolithic) database? If users are sharing a single-file database then I would look into whether the query is open at the time by someone actually looking at data while another person is trying to change the layout.

If this were a split database, I doubt you would get such prompts, which is why I asked the "split" question.

 

[2RUEXX]

Hello,

Thanks everyone for your replies. So, if these queries they are accessing were forms, perhaps then it would not be giving the "save as" prompts occasionally?

@The_Doc_Man - Each user uses a separate copy of the database file so that multiple users are not in the same file. But it is possible that this user might have been in a file which someone else also had opened and perhaps that is why it occasionally is prompting for "save a copy". Thanks for suggesting that. I might test that out.

 

[GPGeorge]

".... So, if these queries they are accessing were forms, perhaps then it would not be giving the "save as" prompts occasionally?..."

That is a non-sequitur. Form's can have queries as their recordsource. I was wondering if the queries which prompt users to save changes are used that way, and, more to the point, if the user's are editing those queries by opening the form's in design view and changing the queries there. If so, that's a high risk practice.

 

[The_Doc_Man]

Or even setting the form's filters and closing after use, because I believe that would also be a cause to save something... the filter.

 

[2RUEXX]

".... So, if these queries they are accessing were forms, perhaps then it would not be giving the "save as" prompts occasionally?..."

That is a non-sequitur. Form's can have queries as their recordsource. I was wondering if the queries which prompt users to save changes are used that way, and, more to the point, if the user's are editing those queries by opening the form's in design view and changing the queries there. If so, that's a high risk practice.

Ok, I am thinking that @The_Doc_Man's theory as to multiple users having the file open may be the cause of the issue. Thanks for your help. We have been allowing users to modify queries directly. It seems like it has worked for us, although I understand now that it is dangerous and not ideal or standard practice.

 

[Pat Hartman]

nd not ideal or standard practice.

That's an understatement. Now that you understand how dangerous it is, it would be best to replace the queries with reports which cannot be modified or with forms that either validate changes or prevent them. When my users want to play with data, I create exports to Excel to solve the problem.

 

[2RUEXX]

That's an understatement. Now that you understand how dangerous it is, it would be best to replace the queries with reports which cannot be modified or with forms that either validate changes or prevent them. When my users want to play with data, I create exports to Excel to solve the problem.

Ok, thanks Pat.