In the first 3 parts of the series I’m writing, I’ve shown the first 4 steps in a deployment script I’m writing for a customer application. Here are the steps I’ve gone over so far:

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.

Next is Step 5: Delete temp table data.

During testing and development, I have a number of temp tables that end up with data in them. In order to tidy up the new distribution, I make sure I delete records from all the temporary tables. In some cases, if your code builds the tables, you can adapt the code to delete the tables. I’m using SQL statements here which allows me to use one liners to do this. I have experimented with creating temporary tables and deleting them afterward, but I’ve had issues when needing to quickly delete the temp table to recreate it. However, it is a worthy endeavor.

In our Access Jumpstart library we have a routine based on Daniel Fye’s technique as presented in this article:

https://www.experts-exchange.com/articles/9753/Creating-and-using-Temporary-Tables-in-Microsoft-Access.html

Although, I’m not using that function here, it’s a great way to manage working with temp tables in your application.

Now for the delete code:

Public Sub Deploy()

   ' ...
   Dim dbLive As DAO.Database
   Set dbLive = DAO.OpenDatabase(LiveFilePath)
   ' ...

   ' Step 5 - Delete temp table data
   dbLive.Execute "DELETE FROM tblTempTable"
   dbLive.Execute "DELETE FROM tblTempTable2"
   dbLive.Execute "DELETE FROM tblTempTable3"
   dbLive.Execute "DELETE FROM tblTempTable4"
   dbLive.Execute "DELETE FROM tempAJSmenuDef"
   dbLive.Execute "DELETE FROM tempLogTable"
   dbLive.Execute "DELETE FROM tempLogTableX"
   dbLive.Execute "DELETE FROM tmpCofC"
   
   ' Cleanup variables
   dbLive.Close
   Set dbLive = Nothing

   ' ...

End Sub

Not too complicated I don’t think. We continue using the new database file reference from Step 3 and then just execute DELETE SQL statements on the live tables we want to remove the data from.

At this point in the script, I’m done using the object to update the database any further, so I clean up the references. This is always a good idea in order to not run into occasional memory leak issues, improper Access shutdowns, and the like.

In the next article I’ll continue on to show the next steps.

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

Step 7: Compact, Decompile, then Compact again.