What is my process for working with a customer database?

Let’s say I get a new database to start working with.

Before I make any changes, I want to put the database into version (source) control. Why? I want to make sure I have any changes I make to the database recorded so I know what I did.

Source control requires text files to work well. The source control system tracks all the files and folders in a particular root folder. It knows if you add a file or change one. It can compare what the contents of folders and files was after the last commit to the repository and tell you what has changed. You can then commit those changes to the repository and this sets up a new mark for where you are right now. It typically also has some kind of log message, commit number, and the date of the commit.

So when I receive the new database I need to create a new source control (version control) repository.

I use Subversion (SVN) for my version control. This has one central repository to which all changes are committed. While the repository stores the changes and history of the files, on my computer I have a working copy. This contains the files as they were in the latest repository version. Then as I change the files, I can decide when to commit them next.

In the meantime I can have another working copy on my laptop, or another computer. You can have any number of working copies.

In this way, I simply update the working copy on my laptop to get the latest changes committed from my desktop. This makes sure I am working with the latest version of the system.

• One SVN Repository: In this system there is only one repository which I have on a server. It is storing the first version of the repository and every change I commit to the system which may include new files, deletion of files, new folders, modified files, etc.
• Multiple SVN Working Copies: Whenever I want to do work to the system on a computer, I can checkout a Working Copy of a particular version (usually the latest) from the SVN Repository. This copy just is using a particular version of the files and when I commit any changes I make back to the repository, it then is using that new revision from the Repository. If I commit changes from another working copy on a different computer, I have to update the original computer’s working copy to get the changes that were made so that both systems are now in sync.

Hopefully that helps to sum up the differences between an SVN Repository and an SVN Working Copy.

As I mentioned in my previous discussion on this topic, Access is a binary file, not a text file. So in order to use versioning I start by:

1. Creating a new SVN Repository for this database on my server.
2. Use the OASIS SVN Access Add-In to export all the different objects (including but not limited to table structure, forms, reports, queries, and code modules) from the Access file to a source folder.
3. Then I use that source folder to create the first revision in the new SVN Repository created in step 1.
4. Finally, I have to delete the source folder and then Check Out the folder from the Repository. It’s exactly the same files, but I use TortoiseSVN to set it up as a working copy.

At this point I have the Repository setup and my first working copy set up. I often will also use 7zip to make a zip file of the Access database and store that to the Repository as well. This is so I have an official backup copy. As I make changes, I save the objects I’m working on often using Ctrl-S which saves the object. OASIS SVN hooks into the Ctrl-S key and exports the object I’m saving to the source folder and then I can commit the file to the repository to create a new revision.

More questions arise, such as:

1. How often do I commit changes?
2. What about SQL Server back end databases?
3. What about the data in the tables?

I’ll discuss those in more detail in the future.

For now, you can visit the following links for more information on the tools I’m citing:

DEV2DEV – The home of oasisSVN

Home · TortoiseSVN – A Windows based SVN Client. This can be used to create and manage your Working Copies and you can even create file based SVN Repositories and interact with SVN servers.

Apache Subversion – The source code and binaries for various linux servers. Explore here to see how you might set up a web server with Subversion.