Setting up Backend DB Security

[R2D2]

I'm trying to figure out how to secure my backend Access database. The backend database stores all the tables I use in my frontend database application. I need to figure out a way to secure my backend DB so that my frontend application can always link to the tables (regardless of who's logged into my frontend DB), but noone (except me) can access the tables in any other way (either by opening the DB directly, or trying to import or link to them from another DB). Anyone got any ideas? I tried to setup password security on the backend DB, but that kept my frontend application from being able to link to the tables, since I didn't know how to set up my frontend app to pass the password to the backend so that it can still link to the tables.

Any help will be much appreciated!

 

[RichMorrison]

This is what you need to do:

1) Secure the back end so only the Admin group has rights. Sounds like you have done this.
2) Log in as an Admin group member and make the front end. Give members of the User group read permissions on forms, reports, etc.
3) Create queries to expose the table content. Use the queries as the RecordSource for forms and reports.
4) In each query, in Properties; set the option "With Owner Access". This means that the query, created by you(an Admin), has the rights of the Admin group.
5) Assign members of the User group read permission to queries.

At the end, you have a secured back end that can be viewed by members of the User group only with queries.

RichM

 

[R2D2]

Thank you for the suggestions. Someone actually directed me to a Microsoft Security FAQ, where I found a tidbit of code that did the trick.

Here's the information:

43. How do I open a password-protected database from Visual Basic?
If you have protected a database using the simplified password-based security, you can open the database by using Visual Basic 4.0 and later using code similar to the following (assuming the database is located at "c:\path\database.mdb" and the password is "curry":

Dim wrk as Workspace
Dim dbProtected as Database

Set wrk = DBEngine.Workspaces(0)
Set dbProtected = wrk.OpenDatabase("c:\path\database.mdb", _
False, False, ";PWD=curry")

This same code will also work with Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000 when attempting to open a password-protected database programmatically. (The above code won't work with Microsoft Access 2.0 or Visual Basic 3.0, because the Jet 1.x and Jet 2.x database engines don't support database passwords.)