Solved

only allow users to access data through queries NEVER DIRECT TABLES

Posted on 2013-12-22
7
400 Views
Last Modified: 2013-12-29
hey guys, i have an inkling that a best practice is to always use a query to allow users to access data and NEVER a table.

here's an example. i have a staff table which i would like the users to edit. without over-engineering things, i am thinking of just exposing the table to them to edit (treating it like a spreadsheet). however i have somehow think that i should always create a query first and only let the users edit the table's data through the query --> even though it's an exact 1 to 1 field mapping.

Question --> 1) how can i expose a table to the users and not allow them to edit the design of it?
2) what are the reasons why i should ALWAYS use queries to show users data or let them edit it, even though it's a 1  1 field mapping?

thanks in advance guys! = )
0
Comment
Question by:developingprogrammer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 275 total points
ID: 39735022
Question --> 1) how can i expose a table to the users and not allow them to edit the design of it?
Your database should be split for sharing with multiple users. If it is, they can't edit the table design from the front end.

Another reason to always split your application.



2) what are the reasons why i should ALWAYS use queries to show users data or let them edit it, even though it's a 1  1 field mapping?

NONE!

I never, never, never let a user edit data directly in a table or query.


I only let users use a form for record maintenance.  

TIP: It can set up a form to look like a spreadsheet or  table view.

By always  using a form you have all the properties and events that can be used to help the user and protect the data.

Letting a user edit record directly in a table or query is very dangerous.


Here is just one example why:
I have a client that has their own database they built.  One user figured out how to create and open a query so they could export data to excel.  They set where conditions to get the records they wanted to export to Excel. There were a few records that they could not figure out how to exclude. So they just highlighted the  records and deleted them from the query results. They thought that this was safe since a query did not actually store the data. Well needless to say I got a frantic 911 user emergency call because they had deleted the data!  Thankfully I was able to restore the data from a backup.

This is unfortunately a more common occurrence that you may think. Several times the  the users blamed it on Access losing their data.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 50 total points
ID: 39735119
Ditto, ditto, and ditto.

Jim.
0
 

Author Comment

by:developingprogrammer
ID: 39735303
whao great! thanks so much TheHiTechCoach!!

and one more situation:

would you link a form to a table or all form MUST be linked to a query?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:developingprogrammer
ID: 39735358
and one more scenario too!

would you allow a user to edit data through a query directly or would you always wrap a form around the query?
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 275 total points
ID: 39735455
1) would you link a form to a table or all form MUST be linked to a query?

I always use a query because  I almost never want to return all the records and fields from a table.    

TIP: Access forms and reports always uses a query.

TIP2:  Avoid a Select * query. Select as few records and fields as possible.
 
If you set the record source to a table then Access is forced to use a Select * query to retrieve all the record from the table.

I design my application's record maintenance forms to only load the record that is being edited.

2) would you allow a user to edit data through a query directly or would you always wrap a form around the query?

ALWAYS a form.

Repeating from my previous answer above:
By always  using a form you have all the properties and events that can be used to help the user and protect the data.

If you want to allow the users to be able to easily destroy the data then use a table or query.

Repeating from my previous answer above:

Letting a user edit record directly in a table or query is very dangerous.

0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 175 total points
ID: 39735881
Hi,

it's not only to avoid that users are able to destroy data because of the missing possibility for the application to control what the user does if he directly work with the data on table basis.

Another reason (beneath all the mentioned ones) is that you create a layer between the table layer and the application layer. That means: You can create a query which returns some data from a table which the user can edit, controlled by the application through a form of course, and if you ever need to change the name of a table field or move data from one to another table and so on you can do that without changing the application code as you only need to adjust the queries which uses the table(s). The query can keep using the former naming through aliases so the code doesn't need to be changed.

In case of a database server you can also deny permissions to tables and allow only permissions to views (queries) so you can create specialized views for different usergroups and give them different permissions only using different views which uses the same tables.

Cheers,

Christian
0
 

Author Comment

by:developingprogrammer
ID: 39744462
whao Christian, thanks for your great explanation! and TheHiTechCoach too!

definitely looking at an upscaling potential, i MUST always maintain the 3 layers - tables, views (queries) and forms. this is the only professional way to do it and will definitely save me (and anyone else) a lot of trouble moving forward when we face the same situation. thanks guys once again for you advice!! = ))
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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