Prevent other users from changing queries

[clatham]

I have a database which worked perfectly fine until I took maternity leave and my replacement started deleting things they didn't know about and ended up breaking some of my reports.

I have now managed to get everything working properly again, but I am due to go off on mat leave again in the next few months and I'm concerned that someone may meddle again.

Several people have access to the database and update the data. I'm happy for one of them to have full access in my absence (should they need to create new queries/reports) but how can I stop all users from having this access?

What is the simplest way of doing this - I'm not good at code?

Thanks,
Clare

 

[theDBguy]

Hi Clare. Congratulations on the baby. If you ask me what would be the "simplest" way, it would just be a question of who do you want to work harder in recovering or fixing the database when it breaks. What do I mean by that? Simple. Save a "master copy" of your database before you start your leave and when you come back, restore the "master copy" over whatever they are using, if it's broken. If they added any new queries or modified any of them while you're away, then they (or you) can easily reapply those changes (at least you can control how it's applied this time). Otherwise, without a "master copy," you'll be on the same boat as you are now trying to reconstruct the database after they messed it up in your absence.

 

[isladogs]

Distribute the front end as an ACCDE file to all users except the program admin
Setup user login by username & password

Create different levels of user access e.g.
Level 1 - can read data only
Level 2 - can read/edit data but not edit objects
Level 3 - can create/edit objects such as queries/reports etc but not alter program settings
Level 4 - unrestricted access

Users at levels 1 & 2 should have no access to the navigation pane or ribbon
All user interaction via forms

Levels 3 would be able to see both navigation pane & ribbon but have some items made unavailable
Level 4 has no restrictions

There are many articles about Access security. For example this is on my website:
http://www.mendipdatasystems.co.uk/improve-security/4594461803

Doing all the above will take some time, especially if you are not experienced in the use of VBA. You need to decide what is achievable in the time you have available

The alternative would be to make a secure backup before you go on maternity leave then, on your return, use it for recovery if necessary. Good luck

 

[MajP]

If multiple people have access, I am assuming this is on a shared network drive. If this is the case, at a minimum you really should consider splitting the db. You should do this even without the other concerns you expressed. This is for performance and stability. The tables should be on the network and then everyone should have a front end on their machines with the code, reports, forms, queries. Then if they modify a report on their machine it has no impact on anyone else. That is relatively simple to do.

 

[jdraw]

I agree with the others that you create an "authoritative Master copy" of the FE database and "lock it away"(leave it in custody of your boss/??). As others have said, on your return, you can test the current copy with the authoritative master and determine a course of action.
As MajP said, if there are multi-users, this DB should definitely be split. Good decision even if it isn't multi-user.
Good luck.

 

[The_Doc_Man]

Everything my colleagues have mentioned about splitting and backup copies and other kinds of safeguards is both true and probably doable - but schedules for programming can be stretched out. That coming baby has its own schedule and I guarantee you that it will come when it is damned well ready, not later. So ready or not....

The first thing I would do is somehow get this message to your users (and rephrase it to match your style):

"Hey, gang, I'm going to be busy for a while with cleaning up some domestic messes. Those of you who in the past have mucked with the database, please remember I put things where I put them for a reason. I will leave comments on objects to tell you why they are important. Please don't delete or alter these objects without a REALLY good reason. Understand that the last time this happened I had to waste a lot of time and money reinventing wheels. Let's not do that this time, please."

Then part 2 of this? Visit every object and put a descriptive comment on it to explain why it is used. For example, on a query you could say "Feeds the Weekly Summary Report" or "Exports daily nematode sighting data" or whatever the queries are used for. The objects in a database can have comments, so just add them and notify your folks that they can SEE those comments and understand WHY you don't want them screwing around.

Part 3 is after you come back (if you decide to do so), to fix the database so that your idiot users who screw up wet dreams can't get their hands on anything. This usually means that you distribute a compiled (.MDE or .ACCDE) version that uses a switchboard form. But if you really want to allow users to poke around, look up ways to turn off the ribbon and the object navigation bars so that they can't open something in design mode. There are ways to dynamically enable or disable the ribbon and nav bar, so perhaps you can allow some of your people the option to do that.

But the BEST way to do this is to have two master copies. One is yours and is a developmental copy. You NEVER give this one out to ANYONE. The other is the production copy that goes into a shared folder. Then you look up on this forum the method of running your app from an icon that copies the file from a master location and then launches it locally. That way if they want to screw with their copy, they can, but if they launch via that downloader, they get the "approved" copy fresh every time.

 

[jdraw]

Well said Doc!