Securing Your Access Database

This post is particularly for any of you that use Access in a multi-user environment.

Selecting the Right Security
When developing in Access to serve multiple users, it is important to consider what level of security you need before you get too far down the path. If you need comprehensive, air-tight security, Access is not the right way to go. Think of Access as a small safe where you keep valuables. You can lock it, potentially bolt it down to the floor, but a skilled thief will simply pry it from the floor, take it home, and figure out how to crack it open with enough time. So if you are highly concerned about data security, look to a more heavy duty platform like MySQL, SQL Server, or Oracle on a centralized database server. If you are satisfied that your employees are not likely to (a) attempt to compromise the data in your database, or (b) take the time to become savvy enough to overcome some basic security measures, Access is just fine.

Securing Your Access Database
Since there are many resources to help you with the necessary tasks of securing your database, I'll just hit the highlights before listing further reading.

A) Limit User Navigation

Be sure to give user's access only to the forms, queries, and reports that are essential to their duties. Hide the database window on startup. And if you choose not to complete (D) below, make sure to eliminate the option to open forms in Design View. Pre-2007 Access formats will allow you to establish workgroup security, which you can then use to limit users to specific objects in the database. If you plan to take advantage of the ACCDB or ACCDE formats in Access 2007, you'll need to think of clever ways to achieve the same result.

B) Lock Down Special Keys

Next eliminate the ability for someone to use Special Keys at startup (Startup>>Options on pre-2007 versions). This will ensure they cannot hit Ctrl-G, as an example, to enter the VB Editor and create some malicious SQL routine.

C) Password Protect

Password protect the database, and if needed, put it in a file server directory that is also protected based on the user's Windows login. This is a key step to prevent someone from accessing your database via an ODBC connection.

D) Convert to MDE

This final step can mean you need to be more careful about error handling, and also adds some steps to deploying updates. But converting to an MDE file means users will be unable to delete/add/change forms and reports, or expose your Visual Basic code.

Further Reading

There are quite a few resources out there on the details of securing an Access 2007 database. The two that really give you a full picture are:
How I Use Microsoft Access User-Level Security by Jack MacDonald
Real World Microsoft Access Database Protection and Security by Garry Robinson

Record Level Auditing in Access

We just released our newest versions of UI Builder for Access. It was a huge effort over the past few months, but we're really excited about some of the new features. We've been playing with Access 2007 for some time now, and put in some nice features specific to 2007. But perhaps most important of all, our 3.0 release includes a great new wizard to help people set up record-level audit history tracking in their forms without having to create any VBA code. Now on to the next release...the work never ends!

Not Ready for Showtime...Yet

I posted a few weeks ago on Trackvia, which promised to be a more usable, online database. I played around with it for several days, and this reviewer's take is "not ready for showtime yet." While Trackvia has some very nice features, it is effectively a list-management tool akin to Excel. Without the ability to store information in a normalized fashion, it's difficult to buy their claim that it is a database.

Databases enable you to store information in efficient ways such that you minimize the amount of replication (within the database) for any given data element. Trackvia, similar to Excel, makes you repeat the same information with each record you might want to store. For example, if you have a contact database, you must repeat the same information (e.g., customer name, address) for each related contact.

The leap from where Trackvia is to where they need to go isn't that far, but I'd hold off any serious purchase decisions until they mature. And by then....Google or Microsoft can help them enhance the software after the acquisition.

Macros or Visual Basic in Access?

If you are starting out with a database project and just learning how to create Microsoft Access solutions, one key question is: Should I use macros or Visual Basic (VBA)? We'll just go over some pro's and con's, you make the right choice for your situation.

Using Macros
Pros

• Easy to learn
• Support for conditional statements (if this is true, then perform this action, otherwise do this other thing)
• Easy for users to launch directly from the database window

Cons

• Unable to support advanced functions (writing to files, reading files, manipulating form objects, complex conditional statements, recordset manipulation)
• Cumbersome to migrate to VB if you choose to eventually using VB instead of macros (converting to VB is initially a pain, and so is maintaining both macros and VB simultaneously in many cases)
  

Using Visual Basic
Pros

• Ability to offer many powerful features in your forms and database (including access to Windows APIs for things like retrieving the NT user name, manipulating window appearance, position, etc)
  
• Ability to create extensive error handling to reduce user frustration and handle exceptions (e.g, when a user can't access a given network file share, taking an alternate course action instead of just stopping your processing)
• Manipulate strings and other variables (e.g., change a name like "Johnson, William" to "William Johnson")
• Easier to debug by inserting breakpoints, seeing what the current value of a variable is, and adding complex conditional statements (if...then)
  

Cons

• More upfront time required to learn how to use VB
• If you don't get formal training, more upfront frustration until you master the language

Certainly there are additional items that could be added to each of the categories above (feel free to comment!), but this condensed list is all I could muster, and should give you a decent foundation for making the right decision.

A new twist on databases?

I've been waiting for this to happen, I just expected it to come from Google rather than a startup. But here it is, hosted databases similar to what we're seeing come out of Google and Microsoft for their respective documents/spreadsheet apps online.

I've just started playing around with it, I'll post more as I come to conclusions.

www.trackvia.com

First reactions:

-great concept

-offline access/synchronization is a big need

-security will be an interesting issue...databases generally hold more important information than just term papers

Creating Application Frameworks for Multiple Projects

Once you create one Access database project, you're likely to find another reason to build one. And with each one you create, you'll likely evolve your techniques and refine each new database. A few tips that have helped me in the past, that you don't often find in the MS Access books or help sites:

1) Create a reusable code library. Create an empty database that will serve as your code library. Create a VBA reference to that database in your new project database (open the VBA Editor, click on Tools...References, then locate your file using the Browse method). Then, for each generic function you create that might be used in more than one project, put it in your code library database. Why is this helpful? For one thing, as you improve the VBA functions in your code library, it becomes easier for each of the separate database projects you've created to use that improved code without updating each database. This is especially true if you deploy front-end databases to multiple users. You can place the code library database on a central file server, then reduce the amount of maintenance you need to do on each separate front-end copy.

2) Create a reusable application framework. That is, as you begin to build each latest and greatest database, you'll likely find they have commonalities you can begin to standardize. Whether it's menu design or a popup reminder form, you'll have items that you use over and over again. Those are the ones you'll want to put into a framework template (for lack of a better name). Then when you go to create your newest project, you've got n% of the project ready to go.

3) If you are creating a database for multiple users where each user has a separate front-end, consider placing important configuration data in the back-end database. Some configuration information you might store in a local table (user-level preferences as an example), whereas you will want to put configuration data in a remote linked table if you may need to change it remotely or want the configuration to be the same for all users. For example, the output location of reports, or a parameter that determines whether you want logging turned on or off on the front-end databases.

It was only after creating about 20 different database projects that I came to use this approach, and it saved a lot of time maintaining front-end database copies and starting a new database project. The lessons really served as the foundation for UI Builder, which is intended to give Access developers a ready-made framework to create their own projects so they can focus on important things like creating user-friendly forms and sound database architectures. But whether you use something like UI Builder or create one for yourself, creating a reusable framework is a must if you're going to develop lots of Access databases.

We'd love to promote Janet...but who's going to keep up that database?

Being an innovative member in your org can be a double-edged sword. Watch out for the edge you might not anticipate. Namely, where being indispensable because of that neat time-saver of an application you created turns into the reason people hesitate to promote you. You probably have a plus sign in the column labeled "Helps Drive Efficiency" or "Increases Revenue,", but unless you have someone capable of carrying on your work, you might get a question mark in the "Able to Backfill" column. If you're an IT worker, probably not as much of an issue. But if the standard expectations of your core role don't include any sort of MS Access knowledge, your employer may be in a tough spot.

Here's why. Your standard job role (that is, what your peers are expected to do) doesn't include a requirement for MS Access expertise, VBA knowledge, etc. So the same reason they are thinking of promoting you (you have capabilities beyond your current position) is also the challenge in finding your replacement. Namely, do they have to modify the job posting to indicate the person needs to know MS Access? And if so, do they pay them more or give them a title like "Business Analyst"?

Another potential challenge, albeit a better problem to have, will be that you do get that promotion, but still have to maintain your application. It might sound like a good compromise at first, but as soon as your new job takes hold of you, those little enhancement requests and bug fixes start to lose their luster.

So what can you do?
1) Remember that what you create needs to live on without you. The less "administrator" intervention required for it to continue operating when you've moved on, the better. That means solid error handling and messages, automation, and following best practices (naming conventions, VBA conventions, commenting your code, etc) so someone else can pick up your work and have a chance of being able to fix bugs without calling you.

2) Start cross-training. Find one or two people in your org that are sharp enough to start picking up what you've learned. That might mean giving them a crash course in VBA, table design, etc. While it might seem like it will make you less indispensable, it probably won't. It will secure your future, and let you take long vacations without worry.

3) Consider replacement. Is your solution available off-the-shelf for a reasonable price? Do you have a professional development org that can take up what you've done and replace it with a more robust solution? If so, think about building a business case to show the benefit of replacing your solution. Why? It shows you have business savvy beyond just increasing efficiency with an MS Access database, gives you an opportunity to dive into financial business cases, and may save you future headaches if you're stuck maintaining a database you created seven years ago and four promotions back (I've been there, it gets uglier and uglier to explain to a VP why you're doing VBA instead of your real job).

As always, happy developing!