Make query read-only?

[Joe4]

I created database, in which I have tried to make as easy to use as possible (complete with menus, entry forms, etc.).

I have a button to "Create an Ad Hoc Query". When pressed, this button takes you to a shell of a query that I started (kind of like a template from which the user can build upon).

I would like to keep the user from Saving changes to this query, thus ruining the "template". How can I make this query Read-Only, or keep changes from being saved to it?

 

[LTunnicliffe]

Try this:

1. Make a copy of your database (just in case) [I hate starting advise like this ]

2. In your query go to Tools|Security|User and Group Permissions and click the Groups radio button.

3. For admins leave alone for users uncheck everything but the read design.

Tell me if this works!

 

[Joe4]

LTunnicliffe,

Thanks for the reply. However, at this time, there is no security feature, and everyone opens up the database the same way, with full rights.

I know that this isn't the preferred way. I worked for months trying to set up security (I posted here regarding it before). Due to the required interaction between this database with other programs (mail merges, etc.), the security "hosed" a lot of programs, and it turned into a big mess. It took a while to get the database back to where it is supposed to be, and I don't want to venture down that road again.

Currently, there are only a few users using it, the database is backed up daily, and we will probably switch to a SQL database in the near future (maybe keep this as the front-end).

Anyway, I was just hoping there would be a way make this query read-only through VBA or some other means (without messing with security).

 

[dk]

Hi,

In the query design view, click View, Properties. Change the recordset type from the default dynaset, to snapshot.

Alternatively, if you don't want to change the query properties you can do something like this (assuming you're using VBA to open the query):-

DoCmd.OpenQuery "Your Query Name", acViewNormal, <FONT="'RED">acReadOnly</FONT>

 

[Joe4]

Dan,

Thanks for the reply. The query was actually being opened by a macro. So I went into the OpenQuery step and changed it to ReadOnly. However, that didn't work. When I try to close the query, it asks me if I want to Save Changes. If I click "Yes", it saved them.

I tried the other two methods you described, and with both of them, it still allowed me to save the changes.

I want the user to be allowed to open the query, do whatever he wants, but just not be Save the Changes to the original query.

Any ideas what may be happening, why these suggestions don't work? Since it is being opened from a Switchboard, do we need to make changes both on the Switchboard level AND query level?

Thanks.