How do you use version control with Access develop

Question

I'm involved with updating an Access solution. It has a good amount of VBA, a number of queries, a small amount of tables, and a few forms for data entry & report generation. It's an ideal candidate for Access.

I want to make changes to the table design, the VBA, the queries, and the forms. How can I track my changes with version control? (we use Subversion, but this goes for any flavor) I can stick the entire mdb in subversion, but that will be storing a binary file, and I won't be able to tell that I just changed one line of VBA code.

I thought about copying the VBA code to separate files, and saving those, but I could see those quickly getting out of sync with what's in the database.

Answer 1

i'm using the Access 2003 Add-in: Source Code Control. It works fine. One Problem are invalid characters like a ":".

I'm checkin in and out. Internly the Add-In do the same as the code up there, but with more tool support. I can see if an object is checked out and refresh the objects.

Answer 2

It appears to be something quite available in Access:

This link from msdn explains how to install a source control add-in for Microsoft Access. This shipped as a free download as a part of the Access Developer Extensions for Access 2007 and as a separate free add-in for Access 2003.

I am glad you asked this question and I took the time to look it up, as I would like this ability too. The link above has more information on this and links to the add-ins.

Update:
I installed the add-in for Access 2003. It will only work with VSS, but it does allow me to put Access objects (forms, queries, tables, modules, ect) into the repository. When you go edit any item in the repo you are asked to check it out, but you don't have to. Next I am going to check how it handles being opened and changed on a systems without the add-in. I am not a fan of VSS, but I really do like the thought of storing access objects in a repo.

Update2:
Machines without the add-in are unable to make any changes to the database structure (add table fields, query parameters, etc.). At first I thought this might be a problem if someone needed to, as there was no apparent way to remove the Access database from source control if Access didn't have the add-in loaded.

Id discovered that running "compact and repair" database prompts you if you want to remove the database from source control. I opted yes and was able to edit the database without the add-in. The article in the link above also give instructions in setting up Access 2003 and 2007 to use Team System. If you can find a MSSCCI provider for SVN, there is a good chance you can get that to work.

Answer 3

We developped our own internal tool, where:

1. Modules: are exported as txt files and then compared with "file compare tool" (freeware)
2. Forms: are exported through the undocument application.saveAsText command. It is then possible to see the differences between 2 different versions ("file compare tool" once again).
3. Macros: we do not have any macro to compare, as we only have the "autoexec" macro with one line launching the main VBA procedure
4. Queries: are just text strings stored in a table: see infra
5. tables: we wrote our own table comparer, listing differences in records AND table structure.

The whole system is smart enough to allow us to produce "runtime" versions of our Access application, automatically generated from txt files (modules, and forms being recreated with the undocument application.loadFromText command) and mdb files (tables).

It might sound strange but it works.