Preventing Direct Data Entry In MS Access 2007 Tables

[Hack]

I work for a health care provider and we have a number of incentive programs going for both physicians themselves, as well as physician organizations.

We have specific criteria that needs to be met in order for incentives to be paid out. To get this information, we send out an MS Access database to over 40 physician organizations and ask them to insert a bunch of data on themselves and their doctors, and then send the database back to us. We have specific validation code running on the forms used to input this data. Most of the physician organizations we deal with are good about following the rules. However, we have a few that completely ignores all the forms and just enters their data directly into the backend tables. This by passes all of our validation code and results in errors when we process them which almost inevitable slows down the receipt, or in some cases, disqualifies them entirely, from receiving any incentive they might otherwise be due. Naturally, this causes them to holler, scream and shout, and it always comes down, from our senior management, as something that is our problem, not theirs, and we have to work around it.

Our work arounds are not viewed with kindness and smiles by our Coprporate Audit department.

Using password protection is not an option…this is a mandate from our management.

At this point, we are looking in to locking down the tables. My research tells me that we cannot do that as locking a table would prevent them from adding any data to it thus defeating the purpose of the database altogether. At the moment we have decided to hide all tables, queries, forms, etc and then uncheck “Show Hidden Objects” so they do not appear at all in the navigation bar.

The problem with this is that anybody that knows even beginning Access can unhide everything.

We have already told them not to enter data directly into the tables. We have given them the reason, but for larger organizations it is faster for them to do it this way and they know that all they have to do is cry to our management, and they will get their stuff no matter what.

Holding up their incentives if they do the back end data entry is not an option because our own management has taken that stick out of our hands.

I have not found a way to prevent direct data entry into the tables short of locking them, which we cannot do.

Have I missed something that would be useful in this scenario?

[jggtz]

Maybe the next can help
http://www.accessforums.net/security...bles-6208.html

[Hack]

I have another question that is related to this. What I got out of the link supplied by jggtz is that I can disable the Shift key thus preventing users from getting into the database in design view (there is an auto startup form with this application.)

Can I disable the ability to go directly into the database (by turning off the shift key) but still allow users the ability to create their own queries?

[wakawaka]

Why not just make a switchboard and give them multiple options? Since it is uniform, can't you just make a query form that passes the parameters to a report or query? Perhaps this isn't ideal, but I don't think Access has that level of control.

[Hack]

Even though I don't think we could get management to buy off on this approach, it is certainly worthy of consideration and a bit of playing around with.

Thanks wakawaka!

[wes4dbt]

Here's another approach by removing the Navigation Pane http://www.access-programmers.co.uk/...d.php?t=196271

For what it's worth, it gets interesting on Post #5

[Hack]

Here's another approach by removing the Navigation Pane http://www.access-programmers.co.uk/...d.php?t=196271

For what it's worth, it gets interesting on Post #5

This has some serious possibilities...I will play around with it.

Thanks!!!

[TysonLPrice]

I don't know access but in MS SQL we don't give access to tables to users just stored procedures. They do all the updates.

[wakawaka]

I don't know access but in MS SQL we don't give access to tables to users just stored procedures. They do all the updates.

The problem is the Access doesn't have as much control as MSSQL does over security. I know 2003 Access databases has more customization towards security than 2007, but you would have to research it.