Source Control in Access without SourceSafe

So after starting my new project at work I have made a bit of progress. Some things were a lot easier than I had anticipated, some were more difficult, and a couple were about what I expected. Here is what I’ve gotten so far.

Getting Started

First, I had to decide how I was going to save my work, were I would store it, and how I would reference it from other databases.
My first decision was to create an Access Add-in. That would make my life extremely simple since it would make my module available immediately after loading any database. The only issue was that my application’s database would still need a reference to the add-in in order to use some of its features. I figured that this would be a problem I could handle with a minimum effort.
In addition, I created a quick batch file to copy the add-in to the default add-ins location for Access 2003. This would make it so I would have a nice standard location to reference the add-in from.
Finally, I added a few of my files to a git repository. I did not add the add-in file or the test database I intended to use. The reason for this is that I wanted to use the source control features of the add-in to eventual add its features into source control. 
Now it was time to tackle my first feature.

Keyboard shortcuts

I had initially thought that this would be the easiest task to handle. Access has a simple mechanism for making generic keyboard shortcuts, just create a Macro called AutoKeys and you can set up key bindings to execute Macro commands. I typically just have each key binding call a function in my VBA code. I save the handlers themselves in a standard module called Hotkeys.
Unfortunately, I hadn’t anticipated the issue I would see due to using an add-in. Apparently when an add-in is opened as a database, the AutoKeys macro works fine. If however, it is opened as an add-in or referenced as a library, the AutoKeys macro is completely ignored. After thinking on this, it made sense. The AutoKeys macro has to exist in the current database, not a library. So my first task was to move the AutoKeys macro into the database under development. It turned out to be trivial to export the macro from the add-in to the database. Simply create a menu command in the Tools menu to call a method in the add-in that did the export.
I didn’t initially take in to account that there might already be an AutoKeys macro. I ended up with a lot of AutoKeys1, AutoKeys2, etc. I’m still not quite happy with my solution to this. For now, I just create a backup of the AutoKeys macro called AutoKeysBackup and then overwrite it with the add-in copy. If the backup already exists, I don’t bother doing anything. The trouble with this approach is two fold. First, if I run the menu command to install the macro twice, I will end up with both AutoKeys and AutoKeysBackup being the same macro. Second, If the user already has a macro set up, chances are they want to use it. This isn’t possible if I rename it. The ideal solution would be to try to add my key bindings to their macro, and simply create the macro if it doesn’t exist. In the event of a conflict of keys, maybe I’ll just ask the user what they want to use.
The next trick was the key bindings. While the macro referenced a number of methods in my add-in, the database didn’t know anything about those methods. So as a part of the menu command to initialize the database for development work, I added a few lines to add a reference in the Visual Basic Editor to the add-in. This give the database access to all of the methods in the add-in. This also means though, that I need to be very careful what I make public in the add-in. If I make too many things public, user’s might start trying to use them in the database as normal methods. When we later remove the reference to the add-in (when the database goes to testing or production), their code would break.

Exporting Modules

At this point I figured it was time to start working on exporting my code. The first thing I did was export all of my modules using the Visual Basic editor so that I could source control them.  After that, it was trivially simple to export each module through code:

Private Sub ExportModule(ByVal ModuleName As String, ByVal BasePath As String)
    Dim FileName As String
    
    Application.SysCmd acSysCmdSetStatus, "Exporting module " & ModuleName & "..."
    FileName = MdbDev.Globals.FileSystemObject.BuildPath(BasePath, ModuleName & ".bas")
    DoCmd.OutputTo acOutputModule, ModuleName, acFormatTXT, FileName
End Sub

I have a method that loops through all of the VBA modules and calls this method with the name of each module. All we are doing is build the filename using a Scripting.FileSystemObject from the Microsoft Scripting Runtime, then calling DoCmd.OutputTo with the appropriate options. BasePath just tells the procedure were the module will be saved (which is a Modules folder under the path where the database is stored). One minor improvement I’m thinking about making is to change the extension for class modules to .cls, but that is pretty minor since the file itself does state which type of module it is.

Exporting Queries

Queries were just as easy as modules. In this case, all I really need is the SQL used to generate the query, so that is exactly what I saved. Here I just looped through each query and saved each to a folder called Queries.

Private Sub ExportQuery(ByVal QueryName As String, ByVal BasePath As String)
    On Error GoTo ErrorHandler
    Dim FileName As String
    Dim TextStream As Scripting.TextStream
    
    If Left(QueryName, 1) = "~" Then Exit Sub ' Ignore temp queries
    
    Application.SysCmd acSysCmdSetStatus, "Exporting query " & QueryName & "..."
    FileName = MdbDev.Globals.FileSystemObject.BuildPath(BasePath, QueryName & ".sql")
    Set TextStream = MdbDev.Globals.FileSystemObject.CreateTextFile(FileName, True)
    TextStream.WriteLine Application.CurrentDb().QueryDefs(QueryName).SQL
    
Done:
    GoSub Cleanup
    Exit Sub
ErrorHandler:
    Dim Num&, Src$, Desc$, File$, Context$
    Num = Err.Number: Src = Err.Source: Desc = Err.Description: File = Err.HelpFile: Context = Err.HelpContext
    GoSub Cleanup
    Err.Raise Num, Src, Desc, File, Context
Cleanup:
    On Error Resume Next
    If Not TextStream Is Nothing Then
        TextStream.Close
        Set TextStream = Nothing
    End If
    Return
End Sub

OK, so that is a lot more code, but 90% of it is just error handling. I am manually opening a text file on disk and I want to make sure I close it properly in the event of an error. For the most part the rest of the code is pretty straight forward. We are just grabbing the SQL from the QueryDef and writing it out to a text file. The only thing of real note here is line 6: We are just returning if the query starts with “~”.  The reason for this is simple. If you set the control source of a control on a form or report to a SQL string instead of a canned query, Access generates a temporary query. It prefixes the name of the temporary query with a tilde (~). All I’m doing here is excluding these queries. We’ll include them later when we export the forms and reports, so adding them here would be redundant.

Exporting Tables

The last thing I set up was exporting tables. This is actually two parts. The first is exporting the tables schema and the second is to export the data.
At first I tried to export the schema as a set of SQL DDL statements to be able to recreate the table anywhere. This turned out to be very slow and the logic was far too complicated. So I did a little research and discovered that I was making things more difficult than they had to be. It turns out that the Access application object has a method called ExportXML. If this method is called for a table, you can export the tables schema as a standard XSD file. This was far less work.
As it turns out, this discovery made exporting the data piece of cake. Why? Because the ExportXML method will also output the data in the table at the same time (or as a separate action) into an XML file. Even better is that the resulting XML file can reference the XSD schema file for validation. So now importing an entire table can be done as simply as calling ImportXML and giving it the XML file. It will automatically create the table and load all of the data.

Private Sub ExportTable(ByVal TableName As String, ByVal BasePath As String)
    Dim FileName As String
    
    If Left(TableName, 1) = "~" Or Left(TableName, 4) = "MSys" Then Exit Sub ' Ignore temp and system tables
    
    Application.SysCmd acSysCmdSetStatus, "Exporting table " & TableName & "..."
    FileName = MdbDev.Globals.FileSystemObject.BuildPath(BasePath, TableName & ".xsd")
    Application.ExportXML acExportTable, TableName, SchemaTarget:=FileName
End Sub

Again, there is an interesting point on line 4. Here we are excluding temporary tables, but also system tables. Access uses several tables to track the objects in the database. All of these table are prefixed with MSys. We do not want to output the structure of these tables as they are the same for all databases. We may later want to output their data, but I’m still not certain that is really necessary.
Also, note that the code above is not exporting the data yet. This is because I want to have separate actions to export the schema and the data. The schema should be exported every time we output the database to text files, but the data should only be done on an as requested bases. The reason for this is that you don’t want to output every change to the data while testing. Most likely you’ll just want to get the database in a known good state, export all the data and then not worry about it again.

Conclusion

I did make some short attempts at exporting forms and reports, but this is looking to be far more difficult. The ExportXML method can export both, including their presentation information. However, the trouble is that it can only do so for bound forms and reports. This is an issue since I typically use a lot of unbound forms. Unbound reports are more rare, but for consistency I would like a solution that would work for both. As far as macros are concerned, I’m still trying to find a way to get access to the internals of a macro. I can get the macro document, but it doesn’t tell me what actions the macro executes. These will be my next set of challenges.