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!