Okay so here's what I did and it seems to have worked...
1) Ran the Debug/Compile on the VBA coding (found an extra "end if" that didn't belong to anything)
2) Clicked on the "Office" button
3) Went to Access Options
4) Clicked on Navigation Options and unchecked Tables, Queries, Forms, Reports, Macros, Modules.
5) Then unchecked...
-- "Use Access Special Keys"
-- "Allow Full Menus"
-- "Allow Default Shortcut Menus"
6) Then converted to ACCDE (which it did without any errors this time).
.............................
Going back into the database, I could not use Shift, nor could I see the navigation pane. All shortcut keys were disabled along with the right-click ability. The only menu options being displayed were those directly associated with text editing and print/export options.
Using a blank database...I could access (for import) the Tables and Queries but NOT the Forms, Macros or Modules as they were completely grayed out.
I also tried opening the db in Notepad and a whole bunch of garbage was displayed...so if they really wanna take the time to sort through that mess...then they can have it.
WOOOHOOO!!!!!
So then I guess the only item remaining would be....if I wanted to password protect the Tables to prevent them from copied, I could password encrypt the BE...but would I be able to bypass the password using code?
Not code that would be for each table when it's used or accessed by the FE, but simply for the entire BE?
This code works for the table access but is impractical for my use...
Dim td As DAO.TableDef
Set td = CurrentDb.CreateTableDef("Table1")
td.Connect = "MS
Access;PWD=myPassword;DATABASE=C:\M2Dev\pwProtecte d.accdb"
td.SourceTableName = "Table1"
CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh
If you would like to password your BE, I *believe* (e.g. I have not actually done this) you could set a database password, then use OpenDatabase method in code with your password retrieved somehow*, and passing it in the argument. Once OpenDatabase is done, you can then refresh the links which should work as Access caches the password for the session. See if it works for you.
* If you do decide to use a password and store it in database understand that this is a security flaw. If you store it as a VBA string, a constant, or in table, or properties it can be read plaintext from Notepad. There are few workarounds, however, with their own ramifications so you will have to decide if they are acceptable for you needs:
1) Create your password by concatenating string based on object names like this:
This cannot be read plaintext except for "MyPassword" and the concatenate is compiled into machine code so there's nothing to extract out.
2) Use a hash. There are several VB/VBA code out there that allows you to use hashing algorithm. Of course, you still need to have something to hash and can count on it to get hashed.
3) Set up a pseudo log in for your users and hash their password without their knowledge. This is more secure than #1 and #2 because the password is never saved, and users can even know that their password factors in somehow to unlock the back end database, they would have to know the specific algorithm being used, whether it's salted, which make would in all probability take even longer, too long that you'd have discovered the fact they were trying to break in.
Right, but the runtime's don't come with any design capabilities either so runtime users would obviously not be a concern. The concern is regarding users with or access to full A2007 and using it pirate the design.
If you would like to password your BE, I *believe* (e.g. I have not actually done this) you could set a database password, then use OpenDatabase method in code with your password retrieved somehow*, and passing it in the argument. Once OpenDatabase is done, you can then refresh the links which should work as Access caches the password for the session. See if it works for you.
* If you do decide to use a password and store it in database understand that this is a security flaw. If you store it as a VBA string, a constant, or in table, or properties it can be read plaintext from Notepad. There are few workarounds, however, with their own ramifications so you will have to decide if they are acceptable for you needs:
1) Create your password by concatenating string based on object names like this:
This cannot be read plaintext except for "MyPassword" and the concatenate is compiled into machine code so there's nothing to extract out.
2) Use a hash. There are several VB/VBA code out there that allows you to use hashing algorithm. Of course, you still need to have something to hash and can count on it to get hashed.
3) Set up a pseudo log in for your users and hash their password without their knowledge. This is more secure than #1 and #2 because the password is never saved, and users can even know that their password factors in somehow to unlock the back end database, they would have to know the specific algorithm being used, whether it's salted, which make would in all probability take even longer, too long that you'd have discovered the fact they were trying to break in.
Very interesting...I'll explore this area more and let you know how it goes.
Having said that, I wish to thank everyone who contributed to this thread. All of you made is so much easier for me to locate the answers I needed. It's been very much appreciated.
Microsoft Access 2007 - Security Toolkit
A toolkit has been developed that can be used to provide user/group level security with the new Access 2007 file format. Please feel free to download it and use it for free. We welcome any suggestions for improving it. Download from here.
Microsoft Access 2007 - Thoughts on data security in Microsoft Access 2007.
Microsoft has removed workgroup security (aka user-level security) in this new version of Access where the new format file (.accdb) is used. They have beefed up the database password and encryption, but if the user knows the database password (and they must to be able to use it), how can the developer stop them accessing the data directly (and don't think you can rely on Navigation Pane restrictions!)? Here are some thoughts on how this can be done. The document below explains the reasoning behind a method called vPPC Enhanced (vPPC = Virtual Password Protected Connection) and was written in late 2006. It is included here for background information only as the vPPC Toolkit supersedes it http://www.pdtl.co.uk/Access2007/Access 2007 vPPC.pdf
I see that Morphius commented on this statement but I wanted to make a stronger statement because this is a dangerous misconception that keeps being repeated.
As a follow up to all this conversation, 2007 now offers a free runtime version availible at Microsoft.com.
So you can now offer your DB to people without Access and it will have compiled securety.
Your application is the same whether you run it with the runtime or retail version of Access. While it is true that you cannot view or modify any objects while running the database with the Access runtime engine, there is nothing to stop someone from opening your application with the retail version of Access in which case, they have complete access to everything unless you have locked it down yourself.
Many people have asked Microsoft for a "runtime" version of the database. It is of course not possible to make the database an executable but there should be an option to "lock down" the app for distribution. This "lock down" should have the effect of running the app with the runtime engine and not allow any access to any object whatsoever and not be bypassable. As a safety precaution, the switch could copy the existing database and make a backup first to prevent someone from locking themselves out of their only db copy permanently.
