Solved

only allow users to access data through queries NEVER DIRECT TABLES

Posted on 2013-12-22
7
396 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
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

820 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