Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

only allow users to access data through queries NEVER DIRECT TABLES

Posted on 2013-12-22
7
Medium Priority
?
405 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 1100 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 1100 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 700 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

715 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