Locking Down Databases

[Morphius]

My new CIO has decided that he wants everything locked down with all our Access databases.

He wants to ensure that no user can:
a) see the Access Objects pane. (so cannot see tables, queries, modules, macros)
b) cannot access Design View.
c) cannot right-click.
d) cannot use Shift at the startup of the Db.
e) cannot use another Access db to pull in the objects from a locked database.

Basically, if it is not available to the user from the form, the user cannot do anything other than make use of the options on the form.

With some of this, I don't even know where to begin.

 

[Banana]

Which version are we talking about? 2007 or earlier?

 

[Morphius]

Access 2007

 

[Tezcatlipoca]

I use a similar range of lockdowns for my databases, coupled with multi-user login screens. The attached example is a very simplistic, cut down version of this security, which might go some way to satisfy what you're looking for.

It should be fairly straightforward to follow. Your guiding principle to building a database or front-end database that offers no access to Access is to use popup forms (I like to make them modal as well, but that depends on your design, really).

Take a look at the attached example, take it apart, and feel free to ask about any points you're unclear on.

 

[Darth Vodka]

i'll give you the 2003 answers

a) see the Access Objects pane. (so cannot see tables, queries, modules, macros)

switched off in the start up options
tools>start up

b) cannot access Design View.

switched off in the start up options again

c) cannot right-click.

switched off in the start up options again

d) cannot use Shift at the startup of the Db.

you have to switch this off in code, search the forum for the code
make a copy of the db, mess the code up and you'll disable shift for yourself
i tend to get the code to disable shift if it's my networkID, then you need to open another copy of it...

e) cannot use another Access db to pull in the objects from a locked database.

ah, even if you use a workgroup file, you stand a chance of someone creating a blank db with the workgroup and still linking in

your best bet is create a seperate front/back end and set a db password on the backend and have no tables in the front end

 

[Morphius]

ah, even if you use a workgroup file, you stand a chance of someone creating a blank db with the workgroup and still linking in

your best bet is create a seperate front/back end and set a db password on the backend and have no tables in the front end

I'm not so much concerned with table data as I am with the queries, macros, modules and forms. The tables really only contain data entry but the rest contain operational related coding and/or settings that could be used to recreate the databases.

 

[Banana]

ah, even if you use a workgroup file, you stand a chance of someone creating a blank db with the workgroup and still linking in

Just to note two things: 1) ACCDB does not implement user-level security so it is totally inapplicable. 2) If you can link into the "secured" database with a blank db, then it was not secured correctly.

I'm not sure how much of rest apply to 2007, though and unfortunately don't know enough to be able to definitely answer any to those.

 

[Morphius]

I use a similar range of lockdowns for my databases, coupled with multi-user login screens. The attached example is a very simplistic, cut down version of this security, which might go some way to satisfy what you're looking for.

It should be fairly straightforward to follow. Your guiding principle to building a database or front-end database that offers no access to Access is to use popup forms (I like to make them modal as well, but that depends on your design, really).

Take a look at the attached example, take it apart, and feel free to ask about any points you're unclear on.

VERY VERY COOL. But I am able to use another Access database to essentially copy the form and look at the design view side...thus allowing me to bypass the setup..which is the very thing I want to prevent.

 

[Banana]

Would setting a database password prevent this copying?

(Just to note: I understand that the database password in 2007 is much more stronger than the ones in 2003 and earlier...)

 

[Morphius]

Could we set a password for the database which the autoexec (onload form) handles?

If so, the user would never see the password governing the database unless they tried to import part of the database into another on their own..!?

 

[Banana]

*headdesk*

That was me being stupid. I was still thinking user-level security. It wouldn't work here, and even if you managed to get it in an autoexec, I would have to say that would render the whole thing pointless because they can open it in a notepad and get the password.

 

[Morphius]

*headdesk*

That was me being stupid. I was still thinking user-level security. It wouldn't work here, and even if you managed to get it in an autoexec, I would have to say that would render the whole thing pointless because they can open it in a notepad and get the password.

Wow never thought of opening a database in Notepad before now...tried it and while there was a lot of garbage data, I was still able to find the module commands so I now know what you mean.

So then is there a way to prevent users from using a blank database to import the macros, modules and forms thus bypassing all of the security setup?

 

[Darth Vodka]

Wow never thought of opening a database in Notepad before now...tried it and while there was a lot of garbage data, I was still able to find the module commands so I now know what you mean.

So then is there a way to prevent users from using a blank database to import the macros, modules and forms thus bypassing all of the security setup?

use a back end with a database password and use an MDE for a front end

keep the front end MDB on a personal drive

 

[Morphius]

use a back end with a database password and use an MDE for a front end, keep the front end MDB on a personal drive

Right...which all the databases are already separated into front-ends and back-ends. The front-end is used by many people however and the back-end is kept on a network drive. The back-end is just the tables...which I'm not worried about the table data since the concern is the pirating of the layout and design (which is in the front-end).

Does converting the front-end to an MDE prevent users from pulling the objects into a blank database?

Is there a size limit for converting to an MDE?

How do I convert it to an MDE? The only option I can find is ACCDE (which apparently does have size restrictions).

**Note** You 'guys' are the best...I have spent days looking for many of the answers you were able to provide already...just incredible the wealth of knowledge...thanks all!!

 

[Banana]

Recall that you are using 2007, so what was MDE is now ACCDE. I am not aware of any size restrictions... are you sure your code is compiled completely?

What it does is strip away all source codes and save only the compiled pseudo-code that's readable by the machine but not by humans. I do not think you can import in/out forms/modules, but you may want to test this to be sure, though.

HTH.

 

[Morphius]

Recall that you are using 2007, so what was MDE is now ACCDE. I am not aware of any size restrictions... are you sure your code is compiled completely?

What it does is strip away all source codes and save only the compiled pseudo-code that's readable by the machine but not by humans. I do not think you can import in/out forms/modules, but you may want to test this to be sure, though.

HTH.

This is the message I get when I attempt to make the front-end into an ACCDE format...

"Microsoft Office Access was unable to create the .accde file"

"This error is usually associated with compiling a large database into an MDE file. Because of the method used to compile the database, a considerable number of TableID references are created for each table. The Access database engine can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).

There is no accurate method to estimate the number of TableIDs the Access database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used."

...hurm

 

[Banana]

Well, do you actually have that many objects in your front-end? Even the last front-end I write, which I considered to be quite complex didn't even have 1,000 objects (tables, queries, forms, reports and modules combined) and I can compile it into MDE.

I'm aware of the limitation, but I also know that error messages are not always spot-on in describing the error and is why I asked you if you made sure that your VBA code were compiled before you tried to create ACCDE.

EDIT: Wait, I just remembered something. It's also possible to break the limitation by developing with the file so many times there's so many objects scattered all across in multiple saves so you have several duplicates of same objects. To remedy this, the simple thing to do is to create a new blank database, make sure AutoCorrect is off (google Allen Browne Autocorrect for whys and hows), then import all objects from your original database, compile the code and save it then try to make an ACCDE out of this.

Let us know if this helps.

 

[Pat Hartman]

I found a bug with A2007 that I can't seem to get around as far as locking down the database. I have all the stuff turned off and deliver the app as an .mde for compatability reasons. I haven't tried making an .accde to see if it has the same issue.

If I use the common dialog API to browse for a spreadsheet to import, for example, the navigation pane abruptly unhides itself. Not only does it look crappy because it clutters the screen, it leaves the user able to access objects. I've found several posted code examples which purport to close the nav pain (my sic ) but none seem to work.

 

[Tezcatlipoca]

Blimy, what a busy thread this has been.

To add a little more, Morphius, the example I gave you was a simple .mdb for testing purposes. I personally always split my database into a front end and back end, password protect the backend, then convert my front end to an MDE (Access 2003 here), which locks down the absolute ton of VB code I have running.
When trying to import from an MDE, only Tables and Queries can be grabbed; no Forms, Modules, or Macros.

One other trick you might try, which will probably be enough to fool the casual user, is to right click on each of your objects, select Properties, and set the object to Hidden. You, being the database designer, will of course be able to view them by going to Tools / Options, clicking the Hidden Objects option, but the casual user/importer will have this turned off, so won't see them at all.

Take a look at the new attached file. This is the exact same database I gave you before; absolutely nothing has changed with the two exceptions that the objects are now hidden, and the database is compiled into an MDE. Try importing it to a new database.

 

[Tezcatlipoca]

"Microsoft Office Access was unable to create the .accde file"

I can't speak for 2007, but the similar message when creating an MDE in 2003 is almost always due to a dodgy line of code somewhere.

In 2003, going into code view (ALT+F11), then selecting Debug / Compile... will not only attempt to compile the code in the same way that creating an MDE will do, but it will also stop at - and, most importantly, highlight - any line of code that is screwing up your compilation.

I'm sure 2007 has a similar function somewhere, so take a look.