Help Locking Tables

[Skelly]

I am new to Access 2007. Before doing this the only thing I could do is start a basic database were all you're looking at is tables. I have learned a good bit about access since starting this project. I am now trying to find out how to lock all tables and only allow specific forms to be used by other users. After researching it seems the only thing I can do is hide the tables and objects I don't want the other users to access. This would be ok but I still don't trust that would work. If by any chance the other users were to get into a table and edit somthing, I'm not sure I'd be able to fix it. Does anyone know how to completely lock other users from doing anything other than adding new clients to the database in the form I have set up for them. I have my switchboard ready. I just need to secure the back end of the database. Thank you in advance for any advice you all may have.

 

[Beetle]

The first thing you should do is split the database if you haven't already. The back end should reside on the network and each user should have their own copy of the front end, stored on their local drive. As far as your forms go, you can set form properties like Allow Additions, Allow Edits, etc. to control user behavior while in the form. If you want users to only be able to add new records (not see existing ones) you can set the Data Entry property of the form to Yes. Also, in your start up options, you can set a start form as well as disable short cut menus, etc.

Once you have your form and start up options set, then you should make a .accde (or .mde, depending on which file version you're using) copy of the front end and give that to your users (I would never give a user a accdb/mdb file). This way there will be no way for the users to get to the design, code or tables from the front end.

Depending on the nature of your users (i.e. their level of computer/network knowledge) this may be all you need to secure your application. Ignorance is the first line of defense, but if you have a user that is even somewhat knowledgeable, and is determined, they could probably find the back end on the network and get to the tables that way if they wanted to. If this is a concern for you, you may want to consider using a more secure database, like SQL Server, for the back end. There is only so much you can do to secure an Access database.

 

[rodmc]

Im not sure why you would want to lock a table as such, what would be the point in that other than maintenance (and even then).

If your intention is to lock a table so users cant edit, then your barking up the wrong tree, your users shouldn't see any of the raw tables ever and should never have access to them. They should only ever access the data via forms.

What you may want to do is lock controls on forms, however if you are looking to restrict access based on user permissions then you will need to create a User ID that can be carried through a session, a global variable (GlobalID), this will allow you to filter records based on the user that is logged in. So if Joe has created 50 records say, then when Joe logs in he can only see the records that he has entered by filtering based on his User ID and level of permissions.

 

[Skelly]

Thank you for the input. Both replies sound great now I'll have to figure out which way I'm going to do this. I'm about the only one in this office that knows more than just the basics of a computer and my knowledge isn't far based. I do a lot of my learning by trial and error. Which is pretty much what I've done to get this far. I'll probably be able to finish up over the weekend and I'll let you know how it worked. Thanks again. This is a big help. Now I'm about to do some trial and error on macros. (don't worry I do everything on a practice database)

 

[Pat Hartman]

You can't totally secure an Access database. If the data is that sensitive, you need to switch to SQL Server. If you are just worried about nosy users mucking about, you can slow them down but you can never actually prevent it. Here's a couple of things you can do to the back end.

1. Make a backup before you do anything else!
2. Make a second backup.
3. Put the BE on a network share and don't tell the users where it is.
4. Disguise its name. Change its extension to .accdr so that Access will only open it in runtime mode. Of course they could just rename it back to .accdb but they have to know enough to do it.
5. Using the Navigation options, you can hide all the tables and hide all the groups so if someone actually locates the BE, they won't see anything if they open it.
6. Disable the shift-bypass key.

 

[Skelly]

I did the split and came up with the BE file during one of my trial and errors and seen were I could lock the navigation. That may be the option I go with. I'm not worried about the data because everyone in the office will have access to the data. My main worry is keeping them away from the designs and layouts. I guess the best way to explain is we have a woman in the office that for some reason (not on pupose) she has some of the craziest problems with her cpu. I think she kind of gets what we call click happy. I just don't want that to happen with this database. Thaks again guys

 

[Galaxiom]

We put our back ends on a drive with hidden share name (append a $ character to the share name). We use a Group Policy to disable browsing of UNC paths. They could still get there it they really searched but it keeps most users away from directly accessing the back end.