Solved

Access 2003 field protection

Posted on 2009-07-02
4
210 Views
Last Modified: 2013-11-29
I am having an access table which have few fields. Some of my user had accidently deleted few fields in the table.

Is there any way to delete protect the table. But I want my users to read & write. Also I should have the access to delete the table without any issues.                                
0
Comment
Question by:samithsukumar
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
therealmongoose earned 125 total points
ID: 24763823
check out this link which explains access user level permissions and security:

http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Faccess%2Fcontent%2Fsecfaq.asp
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 24763862
In short you need to create a workgroup infromation file that is associated with your database - this defines the permissions that different users have over the database and can control wether a user is or is not able to delete a record...
0
 
LVL 84
ID: 24764197
As TRM said, the only way to control this is through User Level Security, but please be VERY careful if you go down this path. This is a complex and difficult subject to understand, and is not even available in Access 2007 if you think you might upgrade this to 2007 and use the new format at some point.

The link provided gives a good background, but Joan Wild's page (www.jmwild.com) is a great source for more info, and includes a step-by-step method for correctly securing your database. Merely running the Security Wizard is sure to cause more harm than good, and a thorough understanding of what ULS does (and what it cannot do) is important before you venture down this path.

Note, however, that you cannot specifically protect an individual column in a table. Access doesn't have that sort of granularity. You can move to a more powerful db engine, like SQL Server Express, which has a much more robust security architecture, and would allow you must greater latitude in how you control these things. SSE is free, easy to install and maintain, and integrates very nicely with Access.

Even in a secured Access database, users can still get to and delete tables unless you implement Run With Owner Permission (RWOP) queries for all data access. RWOP queries allow users to get at the data while also allowing the developer to remove all permissions from the tables (so the user, in effect, has no permissions on the tables and thus cannot delete that table, nor any portion). This, of course, increases the workload on you, the developer, but that's the tradeoff for security.

That said: A workgroup file doesn't contain ANY permissions ... all it does is contain Users, Groups, User+Group associations, and Passwords (encrypted, of course). The database you're securing contains all Permissions, and the Workgroup + Database entail the whole security package. Basically, a workgroup file does little more than authenticate the user, define the groups they are joined to, and provide the db with a SID that it can use when reading/writing permissions. The database then uses this SID to determine whether that user (or a Group that user belongs to) has sufficient permissions to perform the requested action.

0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 24764587
Split the database into a front-end/back-end setup and park the back-end on your server.  Build a user interface in the front-end with forms that are bound to the specific tables.  There you can control what the user can or cannot do to the linked table using the form.  You can even create your own user and group tables thus bypassing the outdated built-in Acesss User Level Security.

ET
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 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

17 Experts available now in Live!

Get 1:1 Help Now