Solved

How do I make a query in Access to be not-updateable

Posted on 2010-11-30
14
491 Views
Last Modified: 2012-05-10
I want my queries to NOT be updateable.  Is there a way I can force that?  Thanks.
0
Comment
Question by:DDSchreck
14 Comments
 
LVL 24

Expert Comment

by:fridom
ID: 34246160
In Access 2003 one can use workgroup files, with the proper permissions you can deny the modifying of queries. However you have to see that QueryDef will not work properly also.

The alternative is explained in the Access Cookbook 1.16 "save my Queries in a table for better programmatic access and security.

Regards
Friedrich
0
 
LVL 12

Expert Comment

by:pastorchris
ID: 34246561
0
 

Author Comment

by:DDSchreck
ID: 34249742
Let me clarify the scenario - I was too vague.

I have a new access user who is going to be working in my database.  When he makes a query, I do not want the recordset to be updateable.  I don't want him to accidentally change the data in the recordset returned by the query.  I know if the queries are complex enough, the recordset is not updateable.  Is there something he can do to assure any query he makes produces a non-updateable recordset?
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 34251006

1

Right-click on the top half of the query wizard window

2

Select properties

3

You can set the Read-Only property or you can change the recordset type to Snapshot (which can not be updated).
0
 
LVL 75
ID: 34251151
"When he makes a query, I do not want the recordset to be updateable."
The Key word here is  "He' ... when 'He' makes the query.  aikimark has specified the way to do this; however, that is not going to be bullet proof by any stretch, since anyone opening the query in design view can change that.

So, a more realistic way is to 1) not allow directly 'viewing' (or editing) data view a 'live' query. Instead, you need to create a UI (forms, etc) to work with the data.  This way, based on who the Windows user is, you can set a Form's Allow Edits property to Yes/No accordingly.

mx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34251358
thanks for the points.

to expand on DatabaseMX comment.  You would probably be better off by having a test environment with copies of the production tables.  It wouldn't matter if the mysterious 'HE' wrote and executed an update query, since it wouldn't touch the production data.

=======
Another way to do this is to create your own front-end query wizard that is only capable of creating a SELECT query.

=======
Another way to do this is to have an invisible form with a timer event that kicks off every minute (or few seconds) and looks at the saved queries for any UPDATE, INSERT, DELETE, or MakeTable queries.  The timer event code could hide or delete the querydef.
 
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 75
ID: 34251425
"Another way to do this is to create your own front-end query wizard that is only capable of creating a SELECT query."
How would that prevent this ?

Another way to deal with this is ... A Form that you select a query form say a List box, then use this code to open the query in Read Only mode:

DoCmd.OpenQuery "TheSelectedQueryName", ,acViewNormal,acReadOnly

mx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34251478
>>How would that prevent this ?

If this is a user, it might mean that the user has a limited number of application forms for doing their work.  The other windows might be hidden from the user, including the database window.  If the user didn't have access to the MS-Access query wizard, they would be able to create queries using this query-wizard substitute.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34251497
One of my applications allows users to create their own SELECT queries.  The users can name these queries for later use.  The user can see all the queries that they created as well as 'system' queries that I created.
0
 
LVL 75
ID: 34251508
But a stock SELECT query is updatable, unless you set the Recordset Type to Snapshot like you suggested ....

mx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34251908
ah.  The queries can only be used in a read-only setting.  The user does not get to see the query results directly.

If I needed to create a querydef from these, I could programmatically make them read-only via property setting.  I could also include a read-only table.  My favorite trick here is to have a one-row table and add a read-only query of the table, setting the query's RecordsetType property = 2 (snapshot).  Then, I can easily add the read only query in the user-defined query's FROM clause to make it read-only.
0
 
LVL 75
ID: 34251953
" I could also include a read-only table."
In Access?  How do you do that ?

"The user does not get to see the query results directly."
aka via a form then ?

mx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34252047
>>" I could also include a read-only table."

Typo.  Should read "I could also include a read-only query."

Good catch, mx.  Thanks.

=======
>>aka via a form then
They do see the results in a form, but in a non-updateable control (multi-column Listbox).  They can also run a report that is based on their row-selection criterion.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now