Developing in Access

I don’t normally have to write very much code at my job. However, I am responsible for several Microsoft® Access database applications. Lately I’ve been finding myself really wishing that I had a lot of the tools I use writing .Net applications available for Access. For instance, I can very easily put my databases under source control (my current preference is for git). However, if I were to do this, they would be treated as binary files. That means that I lose a lot of the nice features of source management: diffs, patches, and blame to name a few.

Seeing this lack and being a wannabe developer , I decided that instead of complaining I would do something about it. So I’ve come up with a new project for myself. I want to make an Access add-in (*.mda database) that I can simply reference from my database projects and get similar tools as what I currently have for .Net development. In the process, though, I don’t want to reinvent the wheel too much. I want to be able to leverage existing tools as much as possible.
With all of that in mind, here are my initial goals I have for this project. Keep in mind that they are subject to change as I am starting off with only a limited idea of how much of this is actually possible.

1. Export all objects from database to text files such as XML or DDL scripts. This will allow for putting all objects under source control with diff and blame support.
2. Import text files that were previously exported to overwrite/add objects back in to the database. This is to allow for starting from a blank database and importing all objects of a previous source revision to recreate that version. I have no real intention of source controlling the databases themselves, only their structure.
3. Import/Export all data in the database to XML. This will let me create pre-canned test data that I can easily access.
4. Simple unit testing support. The idea being to have something akin to early versions of JUnit or NUnit. Fixtures will be modules (class modules?) that end with “Tests.” Tests will be public parameter-less procedures.
5. Automated build support. I have a very specific process that I follow every time I build a new *.mde file from a database. I always update the current version, I compile all the modules, add a password if required (I never password protect the *.mdb as it slows me down too much), and I add the AllowBypassKey=False
   property to ensure that others are not trying to get in to the database improperly. It would be nice to have this whole process (and maybe a few other things) automated.
6. Keyboard support. I try not to touch my mouse, or at least as little as possible. Some nice Visual Studio type keyboard shortcuts to activate each of the above features would be nice.
7. Target Microsoft Access 2003 since that is the version of Office we use at work.

Now none of the above appear to be impossible to do, but some of them might be pretty complicated.  Unit test frameworks in languages that have reflection are complex enough, I can just image doing this in VBA. But I’ll give it a go and we’ll see what I can come up with.