Version control Access 2007 database and application

Question

I need to version control a Microsoft Access 2007 database and application. Currently everything is contained in a single mdb file.

The application includes:

• Forms
• VBA code
• Actual database

I would assume I need to separate the database from the forms/code. I would like to be able to version control the forms/code as text to support version diffs.

At the moment I don't have access to SourceSafe (I heard there may be some access support) so I would prefer a solution that would work with subversion or git.

Answer 1

I use the code below to extract the vba code from Excel files, you may be able to modify this to extract from Access.

Sub ExtractVBACode(strSource, objFSO, strExportPath, objLogFile)
Dim objExcel
Dim objWorkbook
Dim objVBComponent
Dim strFileSuffix
Dim strExportFolder


Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = true

Set objWorkbook = objExcel.Workbooks.Open(Trim(strSource))

strExportFolder = strExportPath & objFSO.GetBaseName(objWorkbook.Name)

If Not objFSO.FolderExists(strExportFolder) Then
    objFSO.CreateFolder(strExportFolder)
End If

For Each objVBComponent In objWorkbook.VBProject.VBComponents
    Select Case objVBComponent.Type
        Case vbext_ct_ClassModule, vbext_ct_Document
            strFileSuffix = ".cls"
        Case vbext_ct_MSForm
            strFileSuffix = ".frm"
        Case vbext_ct_StdModule
            strFileSuffix = ".bas"
        Case Else
            strFileSuffix = ""
    End Select
    If strFileSuffix <> "" Then
        On Error Resume Next
        Err.Clear
        objVBComponent.Export strExportFolder & "\" & objVBComponent.Name & strFileSuffix
        If Err.Number <> 0 Then
            objLogFile.WriteLine ("Failed to export " & strExportFolder & "\" & objVBComponent.Name & strFileSuffix)
        Else
            objLogFile.WriteLine ("Export Successful: " & strExportFolder & "\" & objVBComponent.Name & strFileSuffix)
        End If
        On Error Goto 0
    End If
Next

objExcel.DisplayAlerts = False
objExcel.Quit

End Sub

Can you extract the forms as XML perhaps?

Answer 2

Access 2007 has a feature where you can split a DB into its Tables/Queries (backend) and Forms/Reports (front-end). Since your question mentions only version controlling the forms and modules, this might be a more elegant solution. I don't know where modules go after the split, so that might be a stumbling block.

Microsoft offers VSTO (Visual Studio Tools for Office), which will let you develop in VS and run version control via any VS plugin (CVS/SVN/VSS/etc.).

Finally, you can just directly connect to Visual Source Safe. This MSKB article has some good information and background to go through, while this Office Online article is designed for getting you up and running.

Ultimately, I would suggest against taking the code out of Access if at all possible. Assuming the VBA editor is your primary development environment, you'll be adding extra steps to your development process that cannot easily be automated. Every change you make will need to be manually exported, diff'd, and stored, and there is no Application.OnCompile event that you could use to export the changes. Even tougher, you'll have to manually import all changed source files from other developers when they do checkins.

Answer 3

Visual Source Safe seems to be the only option when using the developer extensions as it does not recognize my installed tortoise subversion client.

Am I missing something?

Answer 4

I was just about to post this same question and found this thread. However no one really answered it well enough for me. I dont want to use Source Safe but rather want to use SVN. How do I split out things from my MDB or use VSTO to open my MDB in Visual Studio. I use a SVN client in VS if it is possible to use VS to treat my access DB as a project.