I wrote a script to deploy my Microsoft Access application which utilizes my company’s Access JumpStart framework which is available for purchase from our store. In the previous parts I’ve explained how I’ve done the first 5 steps of my script:

Step 1 – Update the version number based on the latest commit to my versioning repository.

Step 2 – Create a copy of the currently running development database into a new folder.

Step 3 – Connect to the new database and set the Access JumpStart specific option override table for the live system option

Step 4 – Make sure the database properties in the live database are set correctly.

Step 5 – Delete temp table data.

And now I will move on to the next step.

Step 6: Remove VBA references used for development and update compiler constants to do late binding.

During development, I have our Access JumpStart library connected as a VBA reference to the project as well as a VBA reference to RubberDuck VBA that I use for testing and refactoring.

Here is their web site where you can download it. I recommend you take a look at it if you’ve not used it before. It’s a free download:

rubberduckvba.com

Anyway, I originally just used the same database reference I was using in step 5 to delete the data from temp tables, but when you use the references object of the database that way it has a nasty side effect of loading the VBE environment and flashing up all the windows in the project. I tried to use Application.Echo False to have Access not update the display, but that didn’t work. The only thing I could find to work was to open the application hidden, and to do that, I needed to use a left shift so it wouldn’t activate itself and show the screen anyway.

Here’s my code for Step 6.

Public Sub Deploy()

   ' ...

   ' Step 6 - Remove references and set compiler constants
   '   This is done with a hidden app otherwise the vbe screen pops up and
   '   flips through a bazillion open windows which is annoying
   Dim HiddenApp As Access.Application
   Set HiddenApp = GetAccessDbRefNoAutoexec(LiveFilePath)
   HiddenApp.References.Remove HiddenApp.References("AJS_Library")
   HiddenApp.References.Remove HiddenApp.References("Rubberduck")
   HiddenApp.SetOption OptionName:="Conditional Compilation Arguments", Setting:="AJSactive = 1 : AJS_vbWinst = 1 : LateBindTests = 1"
   HiddenApp.CloseCurrentDatabase
   HiddenApp.Quit
   Set HiddenApp = Nothing

   ' ...

End Sub

The code uses HiddenApp to grab a reference to the Access application using a function. I’ll get to that in a moment, but beyond that we utilize the “References” property of the object to remove the references we don’t want from the live file. This code still allows the app to pop up very briefly and also is using system calls to hold down the shift key while the app opens. It doesn’t shuffle through all the VBE windows though, which is what I was going for. The name of the reference is not always completely obvious, but if you loop through them and do a Debug.Print on the Name of each reference, you can discover it.

The next line of code sets Conditional Compilation Arguments which is an Option of the database. I am making sure in this case that LateBindTests = 1. This is so that any tests I have developed using RubberDuck VBA will not trip errors when the code is compiled on the customer’s machine where they don’t have a reference to the program since it is not installed on their machines. Late Binding means that I am declaring RubberDuck testing objects at runtime. If I am running on my dev system and using the tests, I prefer to use early binding and declare the RubberDuck testing objects at compile time so that I can use the Intellisense autocomplete feature of the Access VBE.

Finally, once done removing the references and setting the compilation constants, I close the current database, quit the hidden Access app and set it to nothing to clean up.

The function “GetAccessDbRefNoAutoexec()” is slightly retooled specifically for my app (so it’s not violating his usage policy) from code written by Dev Ashish you can find here:

API: Bypassing Autoexec (theaccessweb.com)

And tomorrow I’ll finish up the series by explaining my final step.

Step 7: Compact, Decompile, then Compact again.