Securing Access application for distribution

[kenman]

I have created a small Access database system for my customer (an OEM manufacturer) that is to be distributed to multiple distributors. I have 1 Main Database and 2 Backend Databases. 1 Backend DB has pricing and specifications, while the 2nd Backend will contain all distributor created data. This will allow me to email the distributor a new version of the pricing and specifications (and front end if required) once per quarter to reflect pricing changes (and programming changes), while not stepping on the distributor created data.
The owner of the OEM manufacturer is very concerned about the competition stealing this system/information, as he has spent lots of time and $$ developing this.

I don't want to enable Access User Level Security on this system, as then when I distribute it, the users will have to have it enabled, and will have to manage it for other access DBs they may have. (or maybe there is a way to do this without enabling 'all' security that I am not familiar with)

I have created 3 MDE files for these (which locks them out of the Forms, Reports, etc). In addition, I have put a Database Password on tables in the 2 Backend DBs. This disallows the users to open these tables natively. The problem is, once I link the front end to these tables and type in my password, the linked tables are then visible to whoever can get in the database window. (if you open the MDE 2x's from the access menu, it will pop up, if you hit F11 it will pop up). I know I can hide the link tables (not really very secure) and there are ways to Disable the method to get to the database window (maybe that is the best way)...but not sure if that is the best way.

I would like to make my application "timeout", so that every quarter (4 times per year), the OEM will have to send out a new version. This is for 2 reasons....they can't create quotes with old Price info....and they can't steal it forever....it will only work for a max of 3 months. I can create code that looks at todays date and compares it to the 'expire date' and pop up msg box that says "Sorry, timed out". If they change Date/Time on computer, they can get around this method. I was thinking when I pop up this message box, I could delete the MDE that has all pricing information (kind of harsh, but would do the job). I wish I could just delete the links to this backend MDE (8 tables that are password protected), then that would accomplish the same thing without actually deleting the MDE. Even if the user could get into the Database window, they could not recreate the links without the MDE password....they could change DateTime, but links would already be gone. They would then call phone number on msg box that says..."Application has expired - Please call 999-999-9999 for updated version. Note:No data has been lost!!!!"

So after giving all of this background, here are my questions

1. Is there a way to dynamically delete links to a backend database using code (I probably could do it in a macro....but don't really like that method)...and maybe create them also (with a password)?

2. If there is no answer for #1, then is there a foolproof way to dis-allow any user getting to the database window? (without creating custom menu bars and disabling F11...and any other short cuts)...if they added a form and and a button with "DoCmd.SelectObject acTables, ,True" the database window would pop up. (I would still like to get to the database window....so could I create super secret handshake?)

3. Does anyone have a cleaver method to do something similar to what I am trying to do.

Thanks in advance,

Ken

 

[FancyPrairie]

By default, all access databases use the System.mdw workgroup file to manage security. However, for your application, you can setup full security on your databases using your own workgroup file. That way, your OEM Access databases still refer to System.mdw and yours would refer to your workgroup. Simply create a shortcut on their desktop and set the target property of the shortcut to, something like:

path\msaccess.exe path\YourFrontEnd.mdb /wrkgrp path\YourWorkgroupfile.mdw

Note that the workgroup file you develop must reside in a location where your OEM can get it to it. It might be as simple as just copying the workgroup file to the users machines along with your databases.

 

[Fubear]

To Answer #2:

Access can be locked down in a number of places.

F11 key can be disabled in the startup options - click advanced and then tick "Disable Access Special Keys".

Forms can be protected by making an MDE - Go to Toolsatabase Utilities:Make MDE, this will compile your modules and forms, disallowing any changes/additions/imports/exports to be made.

Main Menu can be disabled by changing the MenuBar property of each form to =1, preventing access to most other options.

Tables in the backend can be made invisible by right clicking on them, selecting properties and ticking the "Hidden" tickbox. This will prevent anyone trying to link to the tables from appearing on the table list on the page to set links. I cant say what will happen to the currently linked tables tho.

You could also add a starup table to your backend databases with the onload event set to DoCmd.Close.

Holding Shift key down on startup will bypass ALL security, there is VB code to disable the Shift key on startup - just do a search on this site, its a common question.

I think i have covered all the based - as well as the user level security and DB password you already know about - you should be able to completely lock down the database for others to use.

Those are the tools at your disposal, there arnt any more that I know of - Enjoy!