I’ve been discussing how I use version control in 3 previous emails / articles which you can find here:
Access Programming – What I do and Why (source control) | Access JumpStart
Access Programming – What I do and Why (source control part 2) | Access JumpStart
Access Programming – What I do and Why (source control part 3) | Access JumpStart
This is part 4 where I will continue to answer some potential questions. If you have any questions fee free to ask!
The questions I left unanswered in the last aritcle were:
- What about SQL Server back end databases?
- What about the data in the tables?
What about SQL Server back end databases?
You could just as easily ask about MySQL, NoSQL, Jet linked tables, or any other ODBC linked tables.
The fact is that OASIS can export the linkage information and the data information from these tables, but it does not store the structure information. That all remains in the database you are linking to.
So what about that structure? In my case, I create a “Backend_Upgrades” module in which I will write a routine to make the database changes. SQL Server Management Studio (SSMS) makes getting the SQL statements needed to change the database structure relatively simple. In SSMS I can just right click on a table or view I have created or modified and choose “Script To” and then choose either the CREATE or ALTER option. I usually open that in a new query window in SSMS. Then I modify the statement to get exactly what I need.
Usually at this point I have a long multi-line statement or set of statements I want to paste as strings into VBA. I would also like these strings to be quoted.
If I am just doing this once or twice, I will usually manually do it by pasting the strings into VBA, adding quotes and line continuations to make it all one giant string, but if I have a lot of these to do, I have found my free ChatGPT account at OpenAI has been invaluable in quickly parsing these strings for me using the correct prompts. Generally I start asking it to put the following lines into a string variable for VBA using line continuations, then on the next line paste in my giant query.
I can even tell chatGPT if that didn’t work (like if there were too many line continuations) and it can give me multiple statements split up and ready for multiple variables.
Now at this point I have the alter or create statements necessary to make the database structure changes. I have code set up to connect to my dev database or the live database and I just uncomment the connection string I want to use, and then I have set up a variable as an ADODB.Connection called ActDB. All I have to do in the module is add ActDB.Execute in front of the string I want to execute to make the change. Then I run this script prior to deploying all the new code.
Someday I’d like to get fancier and have a way to script the changes and apply them more easily based on the current version of the database structure and what it is getting upgraded to. I have used a framework for PHP that had a nice utility to do this, and perhaps I’ll look at porting that to VBA someday. You have to write the scripts, but the utility would manage determining the version of the database and running all the scripts required to get it to the revision you’d want it to be at.
What about the data in the tables?
In my workflow, I try to only keep data the users are working with in the back end and avoid trying to version that data. Versioning user data is a losing battle when developing and in my opinion quite unnecessary. So in general I just do exports of the data when I want to update my local copy of the customer data to get it to a newer point in time. For SQL Server, I will either script individual tables with various options available in the dialog, or more often, just script all tables / views / user defined functions / stored procedures at once with DROP and CREATE parameters and just load that into my local copy of their data I use for development. I use a completely separate database instance for development.
This definitely could be further elaborated on, but for now, I’ll just leave the data component discussion there.