Hi,

I am looking for some advice. I have created a database and need some advice on the method of deployment and how to go about implementing security (as it will operate in a multi-user environment).

I am considering either splitting the database into a FE/BE or just saving as an MDE file and saving on the network, where everyone can access. If I go for the FE/BE option I am not sure how the speed of the application would be affected on our network despite offering the benefit of updating designs, etc. without really affecting users.

I will have two groups 'Admin' & 'Users' with permissions set on both these. Obviously it will be easier using a workgroup file but am unsure what disadvantages this could lead to in either of the environments above. I don't want to have a great deal of admin overhead, maintaining the file and risk any corruption. I also beleive when this file is set then those users (if using other databases) will adopt the settings defined in this file, which could be a real nuisance to them.

Therefore what would be the best method of applying security??

Many thanks in advance

First, I would definitely split the databae (and move the back end to SQL :D) and then have the users copy the front end to their desktops and open them from their desktops. This shouldn't be a problem if everyone maps to the same network drive where the back end is, using the same drive letters, path etc. This will speed things up.

Also, make sure you index other fields besides the PK, where appropriate (like date fields or user name fields, etc.)

You may also want to disconnect some forms so they load faster.

For security, are you on a Microsoft network? If so, just use the security wizard in Access. You can also implement your own security, which I have done, using a user name and password form.

Also, you may want to consider giving different versions of the front end to each group. This way you can limit access to the back end based on which version of the front end the user has. I have done this as well. Use queries with limited data for the "users" and connect to those on the back end. Alternatively, if you are using disconnected tables, you can clean the data with DELETE queries as it is imported... of course, if you create views on the back end, you can just import the data already cleaned, and thus reduce network data calls.

Any more details?

HTH




mmcdonal

Many thanks for your advice.

We are using a Microsoft network and able to use the security wizard. I must admit I am not entirely sure of the security process. If I use the security wizard, would this make changes to the workgroup file and require me to send this to all appropriate users when I give them the front end of the db? If so I take it I would need to re-distribute when any changes are made to this?

If you implement your own security how would you go about assigning permissions to groups. (I would need to prevent users from accessing forms through the main switchboard)

kind regards
Raymond

I am not sure about what you do with the security wizard since I have never implemented security that way.
As I said, you can have two different versions of the database and limit access by the version.
You can also implement your own security and have two logins, one for each of your groups. Then when they open for their group, the security looks the same as with the wizard. But this is basically two different front ends in one database file.
Definitely split the database and put the back end on the network and the front ends on each users' desktop.
Also, take alook at "Real World Microsoft Access Database Protection and Security" from a! Press, ISBN: 1-59059-126-7

HTH

mmcdonal

Warning. If you are using XP only. There is a problem if your code adds a lot of transactions. XP downloads and uploads the database for each one! Microsoft have just issued a hotfix (this fix will be available in the next update) but say only install it if you need to. (We did as one of our PC's kept crashing - no problems so far.)

I fyou may be affected look for "XP and Access Jet" issues and 28th June 2005.

The hotfix places a registry command which can be edited to stop XP doing such a crazy thing!

May not affect you but forwarned.... We recognised it as an XP issue because the same program ran fine on our one and only Windows 2000 PC.

never too old to learn....