Secure Access / FE - BE Assistance Please

[reidhaus]

I've been reading & printing endless threads on 3 different forum websites for days now and my head is going to explode. I need moron-proof step by step instructions because clearly my brain capacity is on the same pathetic level as Kelly Bundy.

All I want to do is take a new database that I've created, put the back end on the shared server and front end on each employee's individual computer so they can view reports and enter data. As Admin I need to be able to make changes to the database as needed. I've done this before with a prior database several years back but I can't remember how I did it nor can I find any notes I may have kept.

I've been tinkering around with the security wizard, user groups, permissions, etc and all I have created multiple problems.
#1 - Now whenever I open any Access database I am prompted for a logon which I don't want to have to do. I must have created a workgroup for all databases but how do I get rid of that?

#2 - I don't have a clue what the "target" or "start in" should be for the shortcuts on the employee's computers....so really all they are doing is opening up the shared database via a shortcut. The permissions prevent them from doing any serious damage, however, now only one person at a time can be in the database.

The 'help' function in Office is about as helpful as a hernia and the security wizard really doesn't do a very good job at explaining the steps in any detail in order to understand what's going on.

Quite frankly, I'm lost and if anyone out there has the patience to walk me through this I'd be much obliged!

 

[Beetle]

Personally, I think Access Workgroup "Security" is a waste of time and effort. It's complicated to set up, easy to get wrong, and it's really not all that secure. IMO it's a lot of brain damage without much payoff. Perhaps Microsoft came to the same conclusion because it's been deprecated in newer (A2007 and later .accdb) versions of Access (although you can still use it in A2007 as long as you use the older .mdb file format).

If you've got data of a highly sensitive or business critical nature (i.e. it could be potentially disastrous if the data were compromised) then you shouldn't be using Access in the first place (at least not for data storage). You should be using SQL Server or something similar. The other reason to use "security" is to prevent users from having access to certain parts of the application unrelated to the data itself. In that case, here is my version of "security";

1) Disable the Startup options. In Tools/Startup uncheck the options for Use Access Special Keys, Display Database Window,
Allow Full Menus, etc. Make sure you set a startup form, lest the application opens to a blank window, thereby causing the users to think you are an idiot and have created an application that does nothing.

2) Disable the Shift Key bypass. This is relatively simple. Google it.

3) Give yourself an option to easily re-enable the Shift Key bypass if necessary. I usually just use the Double click event of a label somewhere on the main form (or startup form) that opens a small popup form where I can enter a password.

4) Give the users a .mde (not .mdb) copy of the front end.

5) If I've got an application where, for example, the managers need access to certain things that the regular staff is not allowed, then I create another copy and remove or disable some things (like forms, command buttons etc) and give that copy to the regular staff. It adds an extra level of maintenance, but (IMO) it's still easier than dealing with Workgroup Security.

5) Keep a copy of the .mdb front end as a master. When needed, make any design changes to the master file, create another .mde copy and give the updated .mde to the users. There are several methods available to automate the user updates. Bob Larson, one of the moderators here, has one.

The above steps should handle probably 98% of potential "security" problems caused by users. Ignorance is the first line of defense. The vast majority of users have neither the knowledge, nor the desire, to sabatoge the database you created to make their jobs easier. If by chance you have someone on staff who is even somewhat knowledgeable, and is hell bent on mucking around with your application, then Workgroup Security, or any other method, isn't going to stop them. For that problem, I suggest the following solution;

1) Fire the offending ass.

2) Hire someone who's not an ass.

As far as distributing your application goes, the first thing to do (if you haven't already) is to split the application so that the back end (tables) resides on the server and the front end (queries, forms, reports, code) resides on the local machine. There is a built in Database splitter that makes this a relatively simple process. When you split, you'll want to use UNC path naming, rather than drive letter path naming. So the path will look like;

\\ServerName\SomeFolder\YourApp.mdb

instead of;

P:\SomeFolder\YourApp.mdb

If you use My Network (or Network, whatever it's called in your version of Windows) to navigate to the back end folder when you do the split, you will get UNC path naming. Do this before you create the .mde file mentioned earlier. Also, each user needs to have read/write/delete permissions on the folder where the back end will be stored.

Then, each user gets their own copy of the front end (.mde file) stored on their local PC. It should be stored in their My Documents or AppData folder (depending on your version of Windows), not in Program Files or the root of the C: drive. This is because in most business environments users do not have write permissions in Program Files or the root.

In your case, you may want to create another database, import all your tables, queries, forms, etc. then scrap the old one.

I think that about covers it. Besides, Married with Children is about to start. Gotta go.

 

[gemma-the-husky]

you should not have messed with your security file.

access uses a file called system.mdw to control who can do what with a database

this is in c:\windows\system32

changing this can lock you out of your databases, completely with no prospect of getting back. If you want to invoke security, you should create a NEW mdw file called something else, using faciliites within Access.

I am not sure, but you may be able to rename the existing system.mdw, and access may create a new one - but you may struggle getting back in to this database. DO NOT PERMANENTLY DELETE the existing system.mdw

you didn't need to touch security to achieve what you wanted. Search for help on "splitting" databases.

 

[reidhaus]

Okay, well in the interum from when I first posted this thread and read the responses I completely hosed the system. A little knowledge is dangerous - I guess I thought since I had successfully created and secured a database d 5 years ago the steps would have 'come back to me'.
In tinkering I have created workgroups that are useless and if I make the mistake of 'joining' one of them whenever any Access db is opened I get prompted for a logon. I deleted 'admin' from the users group while keeping myself as 'admins', however, that didn't work either and whenever I try to make certain changes I get that stupid paperclip telling me I don't have permission to do so.
With regards to splitting the database, I was told by that nosey paperclip that I first had to convert the file to another version, which I did but that set off an error of some other kind (my brain is mush I can't recall) that was telling me it didn't like the version I had just converted to and needed the version I just converted from. Confused? Well, that makes two of us.
I admit to the possibility of being a complete bumbling boob but that hardly solves my problem(s). Is there a way at this point where I can get into the database, export everything to a completely new database and start the process again?

 

[reidhaus]

I really appreciate your great advice! I just posted below the new predicament I've created for myself. I'd like to scrap the problem db and export all objects into a new one so I can try out your steps but the stupid paperclip keeps telling me I don't have permission. Funny, I've been able to grant permission to everyone but myself!

 

[Beetle]

I don't have an in-depth knowledge of workgroup security (since I don't use it) and would not want to steer you in the wrong direction, possibly causing even more problems than you are already dealing with. This post will bump the thread, so hopefully someone with more experience with this type of issue will jump in.

 

[gemma-the-husky]

I think that is the problem

have you spent a long while on this dbs.? do you have a backup copy?

you may need to start again.

As I said, I would rename your existing system.mdw to something else. I think access may create a new one - which solves all your problems except the current database ....

 

[reidhaus]

Reinstall Access to remove workgroup?

The main problem I seem to be having now is the newly created workgroup which is linking itself to each new database. I thought I had the issue licked when I created a blank database and imported all the objects seamlessly. Unfortunately, the new db is getting linked to that workgroup where the user group 'admins' has been permanently removed from 'admin'. Even when I open up an old, no longer used db that same workgroup w/permissions/accounts has found its way into the system.

Are there any ramifications to simply reinstalling Access and if not, will that allow for the workgroup to default to the original one installed with Access?

 

[gemma-the-husky]

Re: Reinstall Access to remove workgroup?

The main problem I seem to be having now is the newly created workgroup which is linking itself to each new database. I thought I had the issue licked when I created a blank database and imported all the objects seamlessly. Unfortunately, the new db is getting linked to that workgroup where the user group 'admins' has been permanently removed from 'admin'. Even when I open up an old, no longer used db that same workgroup w/permissions/accounts has found its way into the system.

Are there any ramifications to simply reinstalling Access and if not, will that allow for the workgroup to default to the original one installed with Access?

why not try what i suggested in previous posts. just rename your file system.mdw. I think access may create a new one, which would avoid any need to reinstall.