When working with a particular customer, it is very beneficial to have a plan on working on their database and being able to implement your new database changes effectively.

Ideally, you might have a fully automated pipeline that would allow you to test and deploy a new release at the click of a button. But many of us are stymied by customer security or technology issues preventing us from automating it to this level, however, the more you can automate the testing and deployment of new releases, the more often you will do it.

My work flow is generally like this (and this can have variants from customer to customer due to requirements and differences in their environments)

1. When initially working with the customer, I try to set up a development environment on my laptop or in my home office that is similar to their live environment. The development environment has a copy of the live data. I generally have a manual process of some kind to copy their live data to my development system. This might be copying an Access DB backend file from their network, or exporting the tables and data from their SQL Server so that I can import it into my dev environment.
2. If I have to change the structure of the back end database for a new feature. I create scripts to automatically apply the changes to the development environment and test it before I run them in the live environment. Often if the change is this big, I will do it after hours so the customer is not impacted during their work hours. Back end structural changes normally require the users to be not actively using the database since it is highly likely I will be needing them to use the new front end with the new back end.
3. I create deployment scripts to modify my front end database file for the customer’s live environment. Typically this might mean changing certain Access options like turning off the left shift bypass, setting up connections for the live environment, removing references and setting compiler flags to use early or late binding (early for development to get autocomplete, and late with customer so explicit reference is not required to be set to compile), decompiling and recompiling the project for final deployment, and anything else that needs to be done prior to placing the new front end on the network.
4. Make sure the Front End is never opened on the network by multiple people. Create another database or scripts or bat files for copying the Front End down to the user’s pc before running it at all.
5. Use scripts for version checking to make sure the user is running the latest database file.

There is certainly a lot to unpack and the more you can automate for all your customers, the better off and easier you will make your job!