Version control Access 2007 database and application

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

The application includes:

  • Transferring changes from a dev DB to a production DB
  • Database issue (orphaned migrations) when using git flow branches
  • What's the correct way to deal with databases in Git?
  • How to sync local and remote Django databases
  • Git, robots and diverging branches
  • Generic version control strategy for select table data within a heavily normalized database
    • 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.

  • Database structure and source control - best practice
  • What's a good way (or tool) to version control a SQLite database (schema only)?
  • Versioning SQL Server database
  • Database issue (orphaned migrations) when using git flow branches
  • Testing and Managing database versions against code versions
  • Rollback everything
  • 3 Solutions collect form web for “Version control Access 2007 database and application”

    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.

    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?

    I’ve struggled with this same problem. I originally wrote code very much like the existing answer. The trick is to get all of your modules onto the file system, but that method has some drawbacks. Going that route, you can get your forms and reports out of the VBA Projects, but you can’t get them back in. So, I created a library as part of our Rubberduck VBE Add-in. The library I wrote takes care of importing and exporting all of your code to/from the VBA project to/from the repository as you seemlessly push, pull, and commit. It’s a free and open source project, so feel free to download and install the latest version.

    Here is an example of how the library is used. I’ll be adding actual integration with the VBA editor in a future release.

    Dim factory As New Rubberduck.SourceControlClassFactory 
    Dim repo As Rubberduck.IRepository 
    Dim git As ISourceControlProvider
    
    Dim xl As New Excel.Application
    xl.Visible = true
    Dim wb As Excel.Workbook
    
    Set wb = xl.Workbooks.Open("C:\Path\to\workbook.xlsm")
    
    ' create class instances to work with
    Set repo = factory.CreateRepository(wb.VBProject.Name, "C:\Path\to\local\repository\SourceControlTest", "https://github.com/ckuhn203/SourceControlTest.git")
    Set git = factory.CreateGitProvider(wb.VBProject, repo, "userName", "passWord")
    
    ' Create new branch to modify.
    git.CreateBranch "NewBranchName"
    
    ' It is automatically checked out.
    Debug.Print "Current Branch: " & git.CurrentBranch
    
    ' add a new standard (.bas) code module and a comment to that file
    wb.VBProject.VBComponents.Add(vbext_ct_StdModule).CodeModule.AddFromString "' Hello There"
    
    ' add any new files to tracking
    Dim fileStat As Rubberduck.FileStatusEntry
    For Each fileStat In git.Status
        ' fileStat.FileStatus is a bitwise enumeration, so we use bitwise AND to test for equality here
        If fileStat.FileStatus And Rubberduck.FileStatus.Added Then
            git.AddFile fileStat.FilePath
        End If
    Next
    
    git.Commit "commit all modified files" 
    
    ' Revert the last commit, throwing away the changes we just made.
    git.Revert
    
    Git Baby is a git and github fan, let's start git clone.