Solved

only allow users to access data through queries NEVER DIRECT TABLES

Posted on 2013-12-22
7
393 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
Comment Utility
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
Comment Utility
Ditto, ditto, and ditto.

Jim.
0
 

Author Comment

by:developingprogrammer
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:developingprogrammer
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 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

8 Experts available now in Live!

Get 1:1 Help Now