During my day, I will often switch between development, test, data admin duties, running custom reports and more. This also means I often switch between a backend with test data and a live backend. Now since the test data is a copy of the live data, and that copy is usually not more than hours or at the most days old, it is actually quite hard to tell whether its test data or live data I am messing with.
Now when I am actively testing some new features, code or queries, I am (usually) quite good at remembering to make sure I am in the test database before running some script that could potentially mess up stuff.
But sometimes during my day, I will get an interupt from someone who needs me to perform some backend operation to “fix” something. Its those edge cases where it doesn’t really happen often enough for me to spend time coding a specific “undo” feature for it, but still requires a fix from me. So I go into the linked tables and mess about, and change some values. Now sometimes after spending 20 minutes changing something I realise I was still in the test database. (If you ever did something like this, please comment! Let me know I am not alone) Of course this means I now need to redo those changes in the live backend. Waste of time!
So recently I came up with an approach where I rename the application during startup, depending on the user initials. If its myself, I will get a prompt whether to connect to live of test, and rename the database accordingly. Of course regular users never see this prompt, and just get connected to the live database. Its actually quite easy, you only need a few lines of code.
If Environ("UserNAME") = "AEC" Then
If vbYes = MsgBox("Press Yes to connect to PRODUCTION, No to connect to TEST", vbYesNo + vbQuestion, "Developer Dialogue") Then
sD = "TSM-ComTool"
CurrentDb.Properties("AppTitle") = "LIVE"
sD = "TSM-ComTool-Test"
CurrentDb.Properties("AppTitle") = "TEST"
sD = "TSM-ComTool"
CurrentDb.Properties("AppTitle") = "ComTool"
sD is a string variable containing the name of the database on SQL server, but it could just as well be a path to a mdb/accdb backend. FixCon and FixCon2 is code that relinks the tables DSN-less style, thanks to Doug Steele and Ben Clothier.
A thing to note is that the AppTitle property only exists if you have previously set it through the “Current Database” tab of the options panel, or if you have created the property in code. However once its been set once, you can use and modify it without trouble. The thing to remember is that you need to call the (built-in) function RefreshTitleBar for the change to take effect.
Now with this code in place, its so easy for me to see whether I am connected to test or Live. I haven’t made an error like the one described since then.
I hope you can use this blog post, and please do comment and give feedback. Do you have some examples of other ways to use the application title? Or stories like mine, of doing stuff in the wrong database? Then write a comment.
Don’t forget to subscribe for updates!