Best practices for deploying an Access (2007) application

The simplest solution is to give the users an ACCDE of your FE application database. That will prevent them from modifying VBA code and form design. They will be able to create new queries, as well as modify the queries which your application depends on. You could distribute a companion FE database file, which includes the links to your BE tables, and encourage the users to create their ad hoc queries in the companion database. They would then be less likely to inadvertently break your existing queries.

Another approach is to convert the FE to MDB format and set up ULS (user level security) to give your users group the appropriate privileges (read/open, execute, design, ...) on any database objects you want to protect from user modification. (The older MDB format is still fully supported in Access 2007.) The drawback to this approach is that ULS can be challenging; it's easy to get it wrong.

Regarding table design, that's an issue which is not affected by the type of FE you distribute. Table design is a BE issue.

Er, MDE/ACCDE has NO EFFECT on editability of pure Jet objects, i.e., Tables and Queries, and no Macros. It's only code-bearing objects that are rendered uneditable since the MDE/ACCDE creation process strips out the canonical VBA code and leaves only the compiled p-code. Since Tables, Queries and Macros have no VBA code in them, they remain creatable and editable in an MDE/ACCDE. So, there's really no need for the second half of your first paragraph.

David-W-Fenton 2010-10-12 23:52:51

@David Thanks. I haven't been using MDEs recently, and my recollection was way off.

HansUp 2010-10-13 00:32:07

So what's really the difference between a MDE and an ACCDE?

waanders 2010-10-13 07:19:34

And ULS is not possible with an ACCDE? (i'm asking because @HansUp advices to convert to MDB/MDE)

waanders 2010-10-13 07:23:18

MDE and ACCDE are the same concept, but from different sources. MDB -> MDE and ACCDB -> ACCDE. No, ULS is not available with ACCDE (or ACCDB).

HansUp 2010-10-13 12:06:34

Thanks. But ACCDE has no ULS? So newer Access version, less functionality?

waanders 2010-10-13 13:41:44

Correct, no ULS for ACCDE. I don't know that ACCDB/ACCDE has less functionality overall, but does have different functionality. As examples, this new database format includes support for multi-valued fields and attachment fields, and excludes support for ULS and Jet replication.

HansUp 2010-10-13 13:58:26

But how can I control user level security? There must be a Access 2007 equivalent

waanders 2010-10-13 14:32:29

There is not an Access 2007 (ACCDB/ADDDE format) equivalent for ULS. I you want to use ULS, you must use MDB/MDE format.

HansUp 2010-10-13 15:17:11

Microsoft recommends that you use a different data store if you need ULS. But they are, of course, ignoring the fact that ULS is also useful for front-end objects. It's a case where MS really screwed up, in my opinion.

David-W-Fenton 2010-10-16 15:57:49

Too bad, I liked the "old" ULS implementation

waanders 2010-10-18 06:55:05

ansaurus

tags:

views:

answers:

Best practices for deploying an Access (2007) application

related questions