What security measures do I need for a shared DB?

[amorts]

Hi All,

I am in the process of setting up a database in access to capture information involving around 20 people.

Unfortunatly my experience in using access in a sharing environment is limited.

As the users only need to enter information through a series of forms can someone give me some guidance on what areas of the database I need to protect and if the default record locking will be enough to get me by?

Thanks for your help.

 

[Norie]

That's a pretty broad question.

Access has built-in security functionality but using/deploying really depends on many factors.

If the users only need to enter data via forms the first thing you should probably consider is splitting the database to a front end/back end set up.

The front end would contain all the forms, the back end the data.

The tables and/or queries in the back end can easily be linked to the front end without the user having direct access to them.

Sorry I can't be more specific, but this is a broad subject and it's Saturday night.

 

[amorts]

I know it's Saturday night. But I have to deliver this early next week as an interim measure and I am trying to get everything I need together so I can finalise some stuff tomorrow!

At the moment I have 1 database containing all the forms, tables and queries.

I need to put the database onto a shared drive so that everyone can use it to enter information and then I need to retrieve the data to report on it.

You seem to know quite a lot on the access side of things so I would really appreciate your support on this?!!

 

[MyBoo]

The default record locking is enough to get you going, but as Norie stated, what you are asking is very broad.

Splitting the database is the normal and best practices way to go, in a shared environment, but your immediate issue seems to revolve around security. My guess is that you do not have much network security at the moment.. if any. Access has a built-in security tool, but trying to learn that right now will definitely blow your timeframe for delivery.

Make a copy of your database and use the Tools/Database Utilities/Database Splitter to split the data tables from the forms, queries and reports. No need to change the application name... that which the users will access... just add _bkend to the new db being created. Because you made a backup of your original db, I would now delete the tables in the frontend (we are using a copy here) database and then use File/Get External Data/Link Tables to link the backend tables to your forms, queries and reports database. They will have the same names as the orginals so your forms, queries and reports will work the same as before. In Tools/Startup, set Compact on Close so that Access will compact and repair the database everytime the last user to exit leaves the application. Then set up a nightly backup process to backup your backend database. Keep backups of both the frontend and backend databases.

Keep the original copy (before splitting) off to the side... just in case.

 

[amorts]

Thanks MyBoo. Your tips are much appreciated and it was also very clear and straight forward to do.

I do not know how to schedule a back-up of a database though. It sounds excellent and something I definetly need to apply to my situation.

Can you, (or anyone else) give me some extra detail on how to achieve this?

Thanks.

 

[MyBoo]

Assumption: You are using Microsoft Windows.

On the computer where you want to run the backup from... Click on Start/Run... enter NotePad and then click on OK.

My example makes the assumption that your Access application is on the root of C: and it's called Test.mdb.

In Notepad enter the following: Copy C:\Test.mdb Test_bkup.mdb
Save this file as C:\TestBkUp.bat and exit Notepad.

Click Start then Control Panel, then Scheduled Tasks. Place the mouse pointer in the white space of this window and click the right mouse button.
Click once on New at the bottom of the popup that is now displayed. Rename this to whatever you like... it should be somewhat informative.

Right click on this new entry and then click on Properties at the bottom of the popup. Enter C:\TestBkUp.bat in the Run Window... (use name you created for batch file.. not my example). Review and set options according to your scheduling requirments, etc.

What I've explained above is very basic and has a lot of holes in it. For one, it will fail the second time it runs if you have not moved the copied file somewhere safe as it will expect user input to confirm overwriting the existing file if it finds the previous backup. This can be easily resolved, but my intent here is to explain how to perform a backup.

There are many free and $$ backup programs out there that will increment your backups so that you don't have to worry about overwriting an existing backup. They provide other bells and whistles too. You would enter them in the scheduler the same way as explained above.

 

[amorts]

Great, thanks again - I can see what your getting at and I shall set this up tomorrow in work.