Test and Release
Each client has a person responsible for testing and then releasing new versions – The Client’s Project (or Application) Manager.
This is important as it ensures that –
a) The Client has the opportunity to test in their environment for critical use cases before THEY release
b) They share responsibility for any issues that may then arise in production
My applications are designed to know when they are in a Test Environment and connect to the Test Database (Be it Access or SQL Server)
The test data should be a recent backup from production.
Schema Updates
Schema (Data Structure) updates are fully automated. When run against the Database for the first time, the Application will modify tables, fields, constraints, indexes and additions to Lookup tables.
And, if the Database is SQL Server, then Create/Alter scripts will modify or add Server Objects including Stored Procedures, Views and Triggers.
In Development, I modify the Schema (Data Structure) by adding new tables and fields to tables in the Application itself.
For Server Objects, I first modify them in SQL Server Management Studio and then copy the scripts to the Server Object table in the application.
The Database Update is fully automated so that when it occurs in Development, Test and then in Production the same outcome is assured.
No one needs to run any scripts or modify the database manually onsite.
Release
The new version is then released by the client by simply copying the new Application file to a folder on their server (in a pier to pier network this may be just a dedicated machine).
I hard code this folder to be the \Distribution\ sub folder of their shared data folder but it could be anywhere that all users have read permissions to access.
Usually this folder is where their shared Back End Access Database resides, but is typically where documents are kept for either Access or SQL Server databases, so Read/Write privileges are required for the application to write pdf files for example.
Then when they open it in Production for the first time –
• The FE now knows it is in Production and relinks the Tables, Views and Passthrough queries
• When run against the Database for the first time, the Application will run the Database Update (if required)
• And then increment the version number in the Database to match the Application version
When users launch the application,
their application tests to see if it is the latest version and,
if an update is required, it automatically does the following
• Shells out to a little Updater app
• Closes their current copy of the application so it can be written over
and then the updater app (.accdb or accde)
• Copies the new version from the server
• Closes itself and
• Restarts the new version of the application
This all works brilliantly
• No onsite maintenance is required for either an Access accdb database or a SQL Server database
Apart from backups. Even then, backups can be automated fromt the application using PowerShell
and more importantly
• This empowers clients, with the opportunity and responsibility, to test before releasing a new version into their production environment.