I have created a deployment script which is meant to prepare an Access JumpStart application for a specific live production environment. Access JumpStart is the framework Steve Halder and myself use in creating Access Applications for our customers.

I have already gone over the first three steps of the script and what they do in previous posts.

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

On to Step 4!

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

The purpose of this step is to avoid a message when starting an Access JumpStart database which alerts the user that database properties have been changed and asks them to restart the database. I do not want to have that happen.

This alert message is triggered when the actual database properties are not the same as the properties specified in the AJS options. To avoid this trigger, I simply have to make sure the options in the new live database are already set correctly.

Here is the code I have written to do that:

Public Sub Deploy()

   ' ...
   Dim dbLive As DAO.Database
   Set dbLive = DAO.OpenDatabase(LiveFilePath)
   Dim LiveOptionTable As String: LiveOptionTable = "tblAJS_OO_Live"
   ' ...

   ' Step 4 - Set the actual db properties to the correct values for live now
   '   in the live db so AJS won't complain when it opens the live db and sees
   '   properties are already set
   Deploy_SetDbProperties dbLive, LiveOptionTable

   ' ...
End Sub

Private Sub Deploy_SetDbProperties(Db As DAO.Database, OverrideTable As String)
   Dim OverrideRows As DAO.Recordset
   Dim dbCurrent As DAO.Database
   Set dbCurrent = CurrentDb
   Set OverrideRows = dbCurrent.OpenRecordset("SELECT Iif(OO_Selected,OO_OptName,OptName) AS ActualName, " & _
      "Iif(OO_Selected,OO_OptValue,OptValue) AS ActualValue, OptDataType, OptClassType FROM " & OverrideTable & _
      " ot INNER JOIN tblAJSOptions ro ON ot.OO_OptName=ro.OptName WHERE ro.OptCategory='DbProperty'")
   Do Until OverrideRows.EOF
      RDI_TestSetDbProperty OverrideRows!ActualName, OverrideRows!OptDataType, OverrideRows!OptClassType, OverrideRows!ActualValue, Db
      OverrideRows.MoveNext
   Loop
   
   OverrideRows.Close
   Set OverrideRows = Nothing
   Set dbCurrent = Nothing
   HALDb.setDbProperty "AJS_AppVersion", DB_TEXT, AJS_APP_VERSION_NUMBER, Db
   HALDb.setDbProperty "AJS_AppVersionDate", DB_TEXT, AJS_APP_VERSION_DATE, Db
   HALDb.setDbProperty "AppVersion", DB_TEXT, AJS_APP_VERSION_NUMBER, Db
   HALDb.setDbProperty "AppVersionDate", DB_TEXT, AJS_APP_VERSION_DATE, Db
End Sub

So in the deploy script itself, I call the helper sub Deploy_SetDbProperties passing a reference to the live db dao object and the name of the override table.

What this helper subroutine does is to load the options table with the live table overrides for all database property option types. That’s the fancy SQL in the OpenRecordset function.

Once I have the new database properties to set based on the override table, I loop through them. I run the AJS internal function RDI_TestSetDbProperty from our AJS library which updates the database properties. I’m using this function because it does some work to make sure the option values which are text, get converted to the appropriate value for the database property.

After this loop, I clean up the recordset object and db objects. We are done actually setting the options portion.

The Final 4 lines of the helper function use another library function which simply sets a database property. These properties are checked by the version updating system of AJS and are applied at system open. If they change, the user is presented with the database properties have been updated, please close and restart the database. These additional properties being set prevent that from happening.

Ok, we are getting close to the end of the script. There are still a few more steps, but I think I can finish them all up in the next article. We’ll see.

Step 5: Delete temp table data.

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

Step 7: Compact, Decompile, then Compact again.