I was watching this YouTube video by the Access Pacific User Group by Kent Gorrell.

https://youtu.be/IGv5b5XRNiQ?si=BU8zbnQGF9oyohMv

I was impressed with what he did basically building a version tracking system and amongst a slew of other things, built a system to automatically update SQL backends (with extensive testing of course).

The front end checks the version of the backend it’s connecting to and runs a process to upgrade the backend data structures if it’s required for the new version. Kent used a system with a great user interface to do this.

My version of this is simply to write the scripts in SQL and put them into modules in order to run the DDL commands to update the structures, and then run any migration scripts like copying data into new fields and such. Basically, writing VBA functions manually to update the database as needed.

These scripts get saved in my versioning system which also keeps track of much of the information Kent also tracks like which modules of the Access app get changed in each version.

Using Subversion and OASIS-SVN, this allows me to both see all the files / modules that got changed in a particular commit, plus a side by side comparison of the code as it was before and after the update. This is a tool built into Subversion. Subversion is a version control system similar to Git, which I may end up switching to depending on how the planned Git integration goes with the Microsoft Access development team.

Anyway, Kent’s presentation was a great glimpse into how a huge Access app can be written and maintained very well for an enterprise spanning geographic locations!