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 StartedFirst, 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 shortcutsI 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 ModulesAt 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
Exporting QueriesQueries 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
Exporting TablesThe 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
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.