Make 'robust' database

[agibsonsw]

Hello. (Access 2007)

I have a database which is split and used by 15/ 20 staff. I've been asked to make it more 'robust'; for example to prevent people deleting some records. I think she means
'accidently' deleting stuff.

What is the simplest way to approach this (without user-level security which no longer exists). I could make the front-end an ACCDE file which, I understand, will
hide the navigation bar and the ribbon and design views. I would then need to make sure that the forms have buttons for commands they can use. Perhaps I could have
two different accde files, one with forms that do not allow deletions?

Any guidance would be appreciated, Andy.

(P.S. I'm sure that SharePoint publishing the database will not be an option. But would this allow the same level of user-level security that used to be available?)

[JohnH]

Security still exists - but just not with accdb files. But you can use it with mdb files running under Access 2007. This is one of the reasons I still use mdbs. But converting accdbs back to mdb is difficult. Even if you think you haven not used the new features that are not compatible with mdb format, there are probably lots of little things.

Are you worried about people deleting records accidentally? or deleting records at all?
You can set the "Allow Deletions" property to No, then have a "Delete record" command button that makes them confirm that they really want to delete. If they say yes then "allow deletions" is set to yes, the record deleted, then "allow deletions" set back to no again.

Having two versions is best avoided. It makes maintenance much more complicated. You can use code to detect the user (the windows logon rather than the database logon) and change form properties based on who it is, or hide things for some users.

[agibsonsw]

Hello.
That seems a more straight-forward approach. We're concerned about people accidentally doing things rather than full levels of security.
Yes, I can hide the Navigation Pane, prevent layout view, and use environ("username") to check the user.
Thanks, Andy.

[Wendell]

I would also check with the user (supervisor?) who raised the issue and determine more clearly what the concern is. It is fairly straightforward to hide the ability to delete records in the database by not providing a record selection bar on a form, but that implies that all record editing must be done on a form. We always design applications that way, but it does make them more complex. Access databases can be locked down to the point where it is very difficult for even an experienced daveloper to get to the table level, but it requires a fair bit of additional development. If you really want to prevent the deletion of records, you could switch the backend to SQL Server, create a trigger that prevents deletion, and add a boolean field to support logical deletes. Again, it involves added development, but it can be done.

On the other hand, if the concern is with deleting objects in the database (forms, reports, tables, queries), you have taken the first step by splitting the database. The next step is to deploy the front end to each workstation, rather than having a shared front-end. Then if the user inadvertently trashes the front-end, they can simply get another copy and continue working.

[agibsonsw]

Thanks again.
I read that Sharepoint is an alternative means of securing a database (although this is not appropriate for the database under discussion).

How does that work? Surely it means that someone would have to be responsible for administering the Sharepoint server, and users would have to access the database
via Sharepoint and the internet? Andy.

[Wendell]

I don't have any real world experience with Sharepoint, and I'm hoping I can retire before I do. With the current version of Access, I've viewed it as a solution looking for a problem, but Office 2010 apparently has some much nicer tools, and does a better job of handling concurrency issues. The other issue is that you still have some constraints in what the user interface can do in Sharepoint compared to what the native mode forms and reports are capable of in Access. If you have an application where the users are geographically scattered and the Internet is the only access method, it has it's place, but in a small office with everone on a LAN, I think Access/SQL Server is the better solution.

[agibsonsw]

Is Sql Server Express suitable for a small workgroup? Can user-level security be with it? Ta, Andy.

[Mark L]

One simple way I've found to prevent "accidental" deletes is to remove Cascading Deletes from my relationships. This way, if someone was to get to a table and try to delete a record from the parent table, Access would stop them. I always require Deletes be done from a button on a form, which at least makes people stop and think a little (in theory).

[agibsonsw]

Hi.
The database was created using the Time and Billing Microsoft Template. I've just checked and seen Cascade Deletes have been set for a few of the tables.
I agree, I would not normally set this as a default. I would set it temporarily when maintaining the database. Thanks, Andy.

[petern]

I have a database in a social service agency with a similar number of end users. One of the problems from the time I came in (inherited design that was never quite finished - a student final project from a College program) was problems with end users accidentally deleting records. This database doesn't use access security but rather a homemade system of logging in (designed by the previous administrator). Only users with SuperAdmin levels of permission can delete records. It is cumbersome to administer but effective as it forces workers to consider their actions and request a delete rather than letting them react in frustration and accidentally destroy someone else's work. Over the years, as the supervisor and I have identified more areas for user-created problems (adding newly invented spellings for cities, countries, etc without bothering to check whether it was already in there) we have progressively locked the whole thing down. This allows her statistics to be accurate and reduces user-induced problems down to a bare trickle compared to what it used to be.

I also have a church management database in mdb format that does use Access security. It is definitely more robust than above but I've always struggled a bit editing security levels when I need to modify the setup. I suppose it is a lot more straightforward than writing and debugging code, though. The Security Manager AddIn helps a lot.

[Wendell]

Is Sql Server Express suitable for a small workgroup? Can user-level security be with it? Ta, Andy.

Sorry - had a busy day yesterday and didn't get a chance to respond. And yes SQL Server Express is quite suitable for a small workgroup - in fact it will generally offer as good or better response than an MDB if it is properly configured as the back-end. However Access User Security will only let you do some fairly basic things, much as it does for MDBs - i.e. you can restrict users from deleteing records and that sort of thing. But Express also has the standard SQL Server security features based on the user login. So you do have to factor that into what you are setting up.

[agibsonsw]

Hi. Sorry I didn't quite follow that. If SQL Server security uses the user login then doesn't this make it easier to set up as, unlike .mdb security, it doesn't require
setting up separate usernames and passwords? Thanks, Andy.

[Charlotte]

Hi. Sorry I didn't quite follow that. If SQL Server security uses the user login then doesn't this make it easier to set up as, unlike .mdb security, it doesn't require
setting up separate usernames and passwords? Thanks, Andy.

You still have to set up the SQL Server end of it. That means creating roles and assigning users to roles that manage the security permissions they inherit. In other words, you're still setting up the permissions for users and it's on a highly granular level, so if you get it wrong, they may not be able to connect to anything from a single table to the whole database. What's more, you need to know the name of the server and the name of the database to connect to and the connection has to be specified to use the proper connection, trusted connection or ID and password.

[agibsonsw]

Thank you. Not a trivial task then.. Andy.

[Wendell]

Nope, it's not, although it's not really any more difficult than using Access User Security. And it is more secure, and in some respects more powerful. One of the things you can do with SQL Server is to use triggers to record who edited a table, even if they edited it at the table level (not recommended of course). And you can prevent anyone from deleting records in a table if you wish, you can do archiving of edited records to create a permanent history, etc.