How to limit users to manipulating data only through forms.

[xiaowei]

Hi all,

I am really new to the use of Access, or rather, the work dealing with codes and applications. Now I am working with this huge Access 2003 database. How can I possibly create some interface or other means to limit other users to modifying data only through forms. Is there any resource of relevant algorithm or codes that I can look up to? Thank you all...

xiaowei

[maxamis4]

Well there are many ways to limit users. I could give you several links and references to what you need to learn. But I need more information to guide you in the right direction.

You could split the database, tables in one database and forms in another (best practice)

you could create security using access secuirty

you could use active directory

and there are a couple more. What exactly do you want to d?

let me know

[jamjar]

I'm interested in seeing the advice you receive ....

I think it could be done with a Access security on a front-end/back-end set up, and all users prevented from writing to the back end tables (if not, they could always link in from another database), in which case you would set up front end forms based on "Run With Owner's Permission" queries to enable updating and entry of data.

How much of this is familiar to you?

James

[xiaowei]

Thank you both.
Basically what I m doing now is like a survey or evaluation form. So the forms are the questionaire and other related info, and the tables stores all the data or rather answers to the questions. What I intend to do is to split the database into two so that people doing the survey only get the forms, not seeing the data. I feel that is the sensible way right? I did try the split function in Access, but to my understanding, users are only refrained from changing the design of the tables, and they can still read the tables and modify the values. Am I correct in saying that? As in doing it via security set-ups, I find there are quite a number of security levels, which really confuses me. I just have some preoccupation that designing of application should always observe the rule of information hiding or something. May not be relevant here. I would really appreciate your suggestions in designing this thing.
Thank you again.

xiaowei

[jamjar]

Thank you both.
What I intend to do is to split the database into two so that people doing the survey only get the forms, not seeing the data. I feel that is the sensible way right?

Yes

I did try the split function in Access, but to my understanding, users are only refrained from changing the design of the tables,

Yes and no. They can't change the design from within the front-end database you give to them, but if you don't use security, and they don't follow the rules, they can open the back-end and do whatever they want.

and they can still read the tables and modify the values. Am I correct in saying that?

Yes, if you don't use security.

Without Access user security, any user can either open the back-end directly or create a link from any other database they may create.
With Access user security, you can prevent them changing the design of your tables fairly easily, but under normal setup, they would have read-write (append/update/delete) privileges to any table you wish them to update using forms, so they would still be able to open the tables in the back-end, or link to them, and update directly.
To prevent this you would have to deny them append/update/delete privileges as well, but then forms linked to those tables either directly or through normal queries (run with User's permissions) won't allow them to enter or modify data. You need to base all the forms on queries, and change the Query Properties Run Permissions to "Owner's", where the owner has the appropriate table level append/update/delete privileges .
To master the security setup, download and review the Access Security FAQ. Try to implement it - you will probably need to set up new databases after creating a workgroup and logging in as the user you wish to have full privileges, then import the tables/forms etc from the old.
Good luck!

James

PS- Beware: even once you have done this, the workgroup file is vulnerable to being hacked. MS Access security is not industrial strength!

[xiaowei]

Yes Yes and no. They can't change the design from within the front-end database you give to them, but if you don't use security, and they don't follow the rules, they can open the back-end and do whatever they want.Yes, if you don't use security.

Without Access user security, any user can either open the back-end directly or create a link from any other database they may create.
With Access user security, you can prevent them changing the design of your tables fairly easily, but under normal setup, they would have read-write (append/update/delete) privileges to any table you wish them to update using forms, so they would still be able to open the tables in the back-end, or link to them, and update directly.
To prevent this you would have to deny them append/update/delete privileges as well, but then forms linked to those tables either directly or through normal queries (run with User's permissions) won't allow them to enter or modify data. You need to base all the forms on queries, and change the Query Properties Run Permissions to "Owner's", where the owner has the appropriate table level append/update/delete privileges .
To master the security setup, download and review the Access Security FAQ. Try to implement it - you will probably need to set up new databases after creating a workgroup and logging in as the user you wish to have full privileges, then import the tables/forms etc from the old.
Good luck!

James

PS- Beware: even once you have done this, the workgroup file is vulnerable to being hacked. MS Access security is not industrial strength!

Thank you, James, for this very clear and comprehensive answer.
However it appears to me that it would not be so ideal to realize my objectives by using Access, right? It would be rather easier, I guess, if I just give out the excel files and import them back to Access. It is feasible right? I shall re-consider all over... Thank you a lot for your help. Hope you will see this.

xiaowei

[cyberdwarf]

Hi

You could produce input forms with any development tool which can read Access data (using ODBC, or direct drivers). This way, you would not have to worry about any Access security failings.

Just a thought

Steve

[maxamis4]

It would not be advised to start importing from excel. You can do wonders importing, but for the project you have that complex function is not neccsary. Importing only creates more questions about your process. If this is the road you need to pursue we will help you. But for now I would advise against it.

Very simply put most users don't know enough about access to be dangerous with it. So as long as you split the data from the forms, for the most part I think you will be good to go. make sure that when you split the two you put them in different locations as well. its a good practice. Do what we said and link your tables to the backend. This process is relatively simple as long as the backend stays put. Then design your front end. When your done with the front end create an mde which is shown on the link below. The mde is one of access's better accomplishments for security. This should help you get started.



http://www.dummies.com/WileyCDA/DummiesArticle/id-2127.html

[xiaowei]

Thank you experts...
I would try and probably come back for more advice from you

Xiaowei

[jamjar]

Xiaowei, I thought some more about your problem last night and I think that as maxamis4 suggests, if your users are not sophisticated or can be trusted not to play around on their own (ie, you just don't want them seeing the tables and accidentally opening and editing them), to keep your users away from the tables, you can simply change the Startup options (Tools|Startup) :

Create a form you wish to present to all users at startup (use the Access Switchboard wizard if you wish) and set this as the Display Form/Page. Uncheck the Display Database Window option. These steps will keep your users from seeing the tables/queries etc. and put them into the forms immediately. They will still be able to Unhide the database and access tables directly, but will have to go out of their way to do so.

Separate front-end / back-end is still a good idea (and making an mde as maxamis4 suggests is a good idea too).

If you wish, you can lock it down further by unchecking the Allow Full Menus and Use Special Access Keys (F11 will unhide the database window) options. Now the only way the user can make the tables visible is to hold the shift key down while opening the database. (I'm not sure if the Shift key trick works once you have converted to and mde. And bear in mind if you convert to an mde you will want to keep a copy in mdb format to work on.)

I don't know anything about using other forms via ODBC or Data Access Pages. If they can be used to allow data entry without direct write permissions on the back-end, or to otherwise enhance security, I'd love someone to point me in the direction of a good tutorial!

cheers-
James