You didn't answer some of my questions, but I assume your users don't log on, but rather open the database with the default Admin user account. You have given this account permission to do almost anything with the tables except Modify Design and Administer. You are hoping that you can protect your tables by hiding them, so people won't know their names.
This means you're making your security dependent on the ignorance of the people trying to circumvent it. In security circles, this is considered very weak protection. You never know when your table name will be revealed by some process you didn't anticipate.
For example, a savvy user can write code that opens your database in code, and loops through the TableDefs collection displaying all the table names. Your table names will be exposed.
The Database Documentor also lists hidden tables, though if you attempt to print the design information it just prints a blank page.
A user can also import or link your tables into a new Access database, if he knows the names, by using the TransferDatabase action or method.
Apparently Excel ignores the dbHiddenObject attribute all together, which is why you can easily import the table into Excel.
To protect your table against all these exposures, you need to deny permission to open it to the security account(s) used by your users. You should have a separate account that requires a logon password. You need to assign ownership of the table to this account. You will have to use this account to access the table yourself. (Make a copy of the workgroup file and join it, then open up a database and assign a password to the Admin user. You'll need to use this workgroup file to get the logon prompt when you want to sign on as the owner of the table.)
Of course, your forms, reports, and modules need to be able to open and read/update the tables. To do so, they must use OWNERACCESS queries. In Access stored queries, these have the "Run Permissions" query property set to "Owner's". In SQL statements, they include the WITH OWNERACCESS OPTION clause. OWNERACCESS queries are executed using the permissions of the table owner, not the user's permissions. Since the owner always has full permissions on the table, an OWNERACCESS query can do anything; it's up to you to limit what it does (via the query type) and which columns it selects.
Of course, that wouldn't be very secure either, if the user can create his own OWNERACCESS queries, so to complete the picture, you need to block the user from getting into the design view of any object other than a table design grid. (Queries, forms, reports, macros, and modules can all run SQL statements, so you need to block access to design view for all of them.) To do that, you basically need to uncheck all the startup properties--Allow Full Menus, Allow Built-in Toolbars, Allow Default Shortcut Menus, Allow Toolbar/Menu Changes, etc. Since that prevents you from using the built-in menus and toolbars, you have to provide your own, custom menus and toolbars (which can include safe built-in menu items).
I think the only remaining exposure to block is the possibility that users can change their own permissions. You must make sure the following are all true:
1. The users are not members of the Admins group in the workgroup file you were using when you created the database (or this copy of the database).
2. The users are not owners of any objects in the database. If any objects have a group account as their owner, the users must not be members of that group.
3. The users must not have Administer permission on any of the objects in the database.
That about covers it. Let me point out, though, that I've made the assumption that you set up your Access workgroup file correctly. Getting that wrong is the most common mistake made by people trying to implement Access user-level security. There are other security setup details you haven't given me, so I'm assuming those are ok, too. If you've got any of these setup details wrong, all bets are off. But if you can't open the database using an unmodified workgroup file from a fresh Access installation, you're probably ok.
Let me also point out that this is almost a complete implementation of Access user-level security. The only difference, I think, is that your users all use the Admin account and don't have to log on.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
