Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

only allow users to access data through queries NEVER DIRECT TABLES

Posted on 2013-12-22
7
Medium Priority
?
406 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

972 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