How do you use version control with Access development?

Question

I'm involved with updating an Access solution. It has a good amount of VBA, a number of queries, a small amount of tables, and a few forms for data entry & report generation. It's an ideal candidate for Access.

I want to make changes to the table design, the VBA, the queries, and the forms. How can I track my changes with version control? (we use Subversion, but this goes for any flavor) I can stick the entire mdb in subversion, but that will be storing a binary file, and I won't be able to tell that I just changed one line of VBA code.

I thought about copying the VBA code to separate files, and saving those, but I could see those quickly getting out of sync with what's in the database.

Answer 1

I found this tool on SourceForge: http://sourceforge.net/projects/avc/

I haven't used it, but it may be a start for you. There may be some other 3rd party tools that integrate with VSS or SVN that do what you need.

Personally I just keep a plain text file handy to keep a change log. When I commit the binary MDB, I use the entries in the change log as my commit comment.

Answer 2

For completeness...

There's always "Visual Studio [YEAR] Tools for the Microsoft Office System" (http://msdn.microsoft.com/en-us/vs2005/aa718673.aspx) but that seems to require VSS. To me VSS (auto corrupting) is worse than my 347 save points on my uber backuped network share.

Answer 3

It appears to be something quite available in Access:

This link from msdn explains how to install a source control add-in for Microsoft Access. This shipped as a free download as a part of the Access Developer Extensions for Access 2007 and as a separate free add-in for Access 2003.

I am glad you asked this question and I took the time to look it up, as I would like this ability too. The link above has more information on this and links to the add-ins.

Update:
I installed the add-in for Access 2003. It will only work with VSS, but it does allow me to put Access objects (forms, queries, tables, modules, ect) into the repository. When you go edit any item in the repo you are asked to check it out, but you don't have to. Next I am going to check how it handles being opened and changed on a systems without the add-in. I am not a fan of VSS, but I really do like the thought of storing access objects in a repo.

Update2:
Machines without the add-in are unable to make any changes to the database structure (add table fields, query parameters, etc.). At first I thought this might be a problem if someone needed to, as there was no apparent way to remove the Access database from source control if Access didn't have the add-in loaded.

Id discovered that running "compact and repair" database prompts you if you want to remove the database from source control. I opted yes and was able to edit the database without the add-in. The article in the link above also give instructions in setting up Access 2003 and 2007 to use Team System. If you can find a MSSCCI provider for SVN, there is a good chance you can get that to work.

Answer 4

We developped our own internal tool, where:

1. Modules: are exported as txt files and then compared with "file compare tool" (freeware)
2. Forms: are exported through the undocument application.saveAsText command. It is then possible to see the differences between 2 different versions ("file compare tool" once again).
3. Macros: we do not have any macro to compare, as we only have the "autoexec" macro with one line launching the main VBA procedure
4. Queries: are just text strings stored in a table: see infra
5. tables: we wrote our own table comparer, listing differences in records AND table structure.

The whole system is smart enough to allow us to produce "runtime" versions of our Access application, automatically generated from txt files (modules, and forms being recreated with the undocument application.loadFromText command) and mdb files (tables).

It might sound strange but it works.

Answer 5

We wrote our own script in VBScript, that uses the undocumented Application.SaveAsText() in Access to export all code, form, macro and report modules. Here it is, it should give you some pointers. (Beware: some of the messages are in german, but you can easily change that.)

EDIT: To summarize various comments below: Our Project assumes an .adp-file. In order to get this work with .mdb/.accdb, you have to change OpenAccessProject() to OpenCurrentDatabase().

decompose.vbs:

' Usage:
'  CScript decompose.vbs <input file> <path>

' Converts all modules, classes, forms and macros from an Access Project file (.adp) <input file> to
' text and saves the results in separate files to <path>.  Requires Microsoft Access.
'

Option Explicit

const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3

' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

dim sADPFilename
If (WScript.Arguments.Count = 0) then
    MsgBox "Bitte den Dateinamen angeben!", vbExclamation, "Error"
    Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))

Dim sExportpath
If (WScript.Arguments.Count = 1) then
    sExportpath = ""
else
    sExportpath = WScript.Arguments(1)
End If


exportModulesTxt sADPFilename, sExportpath

If (Err <> 0) and (Err.Description <> NULL) Then
    MsgBox Err.Description, vbExclamation, "Error"
    Err.Clear
End If

Function exportModulesTxt(sADPFilename, sExportpath)
    Dim myComponent
    Dim sModuleType
    Dim sTempname
    Dim sOutstring

    dim myType, myName, myPath, sStubADPFilename
    myType = fso.GetExtensionName(sADPFilename)
    myName = fso.GetBaseName(sADPFilename)
    myPath = fso.GetParentFolderName(sADPFilename)

    If (sExportpath = "") then
        sExportpath = myPath & "\Source\"
    End If
    sStubADPFilename = sExportpath & myName & "_stub." & myType

    WScript.Echo "copy stub to " & sStubADPFilename & "..."
    On Error Resume Next
        fso.CreateFolder(sExportpath)
    On Error Goto 0
    fso.CopyFile sADPFilename, sStubADPFilename

    WScript.Echo "starting Access..."
    Dim oApplication
    Set oApplication = CreateObject("Access.Application")
    WScript.Echo "opening " & sStubADPFilename & " ..."
    oApplication.OpenAccessProject sStubADPFilename

    oApplication.Visible = false

    dim dctDelete
    Set dctDelete = CreateObject("Scripting.Dictionary")
    WScript.Echo "exporting..."
    Dim myObj
    For Each myObj In oApplication.CurrentProject.AllForms
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acForm, myObj.fullname, sExportpath & "\" & myObj.fullname & ".form"
        oApplication.DoCmd.Close acForm, myObj.fullname
        dctDelete.Add "FO" & myObj.fullname, acForm
    Next
    For Each myObj In oApplication.CurrentProject.AllModules
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acModule, myObj.fullname, sExportpath & "\" & myObj.fullname & ".bas"
        dctDelete.Add "MO" & myObj.fullname, acModule
    Next
    For Each myObj In oApplication.CurrentProject.AllMacros
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acMacro, myObj.fullname, sExportpath & "\" & myObj.fullname & ".mac"
        dctDelete.Add "MA" & myObj.fullname, acMacro
    Next
    For Each myObj In oApplication.CurrentProject.AllReports
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acReport, myObj.fullname, sExportpath & "\" & myObj.fullname & ".report"
        dctDelete.Add "RE" & myObj.fullname, acReport
    Next

    WScript.Echo "deleting..."
    dim sObjectname
    For Each sObjectname In dctDelete
        WScript.Echo "  " & Mid(sObjectname, 3)
        oApplication.DoCmd.DeleteObject dctDelete(sObjectname), Mid(sObjectname, 3)
    Next

    oApplication.CloseCurrentDatabase
    oApplication.CompactRepair sStubADPFilename, sStubADPFilename & "_"
    oApplication.Quit

    fso.CopyFile sStubADPFilename & "_", sStubADPFilename
    fso.DeleteFile sStubADPFilename & "_"


End Function

Public Function getErr()
    Dim strError
    strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
               "From " & Err.source & ":" & vbCrLf & _
               "    Description: " & Err.Description & vbCrLf & _
               "    Code: " & Err.Number & vbCrLf
    getErr = strError
End Function

If you need a clickable Command, instead of using the command line, create a file named "decompose.cmd" with

cscript decompose.vbs youraccessapplication.adp

By default, all exported files go into a "Scripts" subfolder of your Access-application. The .adp/mdb file is also copied to this location (with a "stub" suffix) and stripped of all the exported modules, making it really small.

You MUST checkin this stub with the source-files, because most access settings and custom menu-bars cannot be exported any other way. Just be sure to commit changes to this file only, if you really changed some setting or menu.

Note: If you have any Autoexec-Makros defined in your Application, you may have to hold the Shift-key when you invoke the decompose to prevent it from executing and interfering with the export!

Of course, there is also the reverse script, to build the Application from the "Source"-Directory:

compose.vbs:

' Usage:
'  WScript compose.vbs <file> <path>

' Converts all modules, classes, forms and macros in a directory created by "decompose.vbs"
' and composes then into an Access Project file (.adp). This overwrites any existing Modules with the
' same names without warning!!!
' Requires Microsoft Access.

Option Explicit

const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3

Const acCmdCompileAndSaveAllModules = &H7E

' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

dim sADPFilename
If (WScript.Arguments.Count = 0) then
    MsgBox "Bitte den Dateinamen angeben!", vbExclamation, "Error"
    Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))

Dim sPath
If (WScript.Arguments.Count = 1) then
    sPath = ""
else
    sPath = WScript.Arguments(1)
End If


importModulesTxt sADPFilename, sPath

If (Err <> 0) and (Err.Description <> NULL) Then
    MsgBox Err.Description, vbExclamation, "Error"
    Err.Clear
End If

Function importModulesTxt(sADPFilename, sImportpath)
    Dim myComponent
    Dim sModuleType
    Dim sTempname
    Dim sOutstring

    ' Build file and pathnames
    dim myType, myName, myPath, sStubADPFilename
    myType = fso.GetExtensionName(sADPFilename)
    myName = fso.GetBaseName(sADPFilename)
    myPath = fso.GetParentFolderName(sADPFilename)

    ' if no path was given as argument, use a relative directory
    If (sImportpath = "") then
        sImportpath = myPath & "\Source\"
    End If
    sStubADPFilename = sImportpath & myName & "_stub." & myType

    ' check for existing file and ask to overwrite with the stub
    if (fso.FileExists(sADPFilename)) Then
        WScript.StdOut.Write sADPFilename & " existiert bereits. Überschreiben? (j/n) "
        dim sInput
        sInput = WScript.StdIn.Read(1)
        if (sInput <> "j") Then
            WScript.Quit
        end if

        fso.CopyFile sADPFilename, sADPFilename & ".bak"
    end if

    fso.CopyFile sStubADPFilename, sADPFilename

    ' launch MSAccess
    WScript.Echo "starting Access..."
    Dim oApplication
    Set oApplication = CreateObject("Access.Application")
    WScript.Echo "opening " & sADPFilename & " ..."
    oApplication.OpenAccessProject sADPFilename
    oApplication.Visible = false

    Dim folder
    Set folder = fso.GetFolder(sImportpath)

    ' load each file from the import path into the stub
    Dim myFile, objectname, objecttype
    for each myFile in folder.Files
        objecttype = fso.GetExtensionName(myFile.Name)
        objectname = fso.GetBaseName(myFile.Name)
        WScript.Echo "  " & objectname & " (" & objecttype & ")"

        if (objecttype = "form") then
            oApplication.LoadFromText acForm, objectname, myFile.Path
        elseif (objecttype = "bas") then
            oApplication.LoadFromText acModule, objectname, myFile.Path
        elseif (objecttype = "mac") then
            oApplication.LoadFromText acMacro, objectname, myFile.Path
        elseif (objecttype = "report") then
            oApplication.LoadFromText acReport, objectname, myFile.Path
        end if

    next

    oApplication.RunCommand acCmdCompileAndSaveAllModules
    oApplication.Quit
End Function

Public Function getErr()
    Dim strError
    strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
               "From " & Err.source & ":" & vbCrLf & _
               "    Description: " & Err.Description & vbCrLf & _
               "    Code: " & Err.Number & vbCrLf
    getErr = strError
End Function

Again, this goes with a companion "compose.cmd" containing:

cscript compose.vbs youraccessapplication.adp

It asks you to confirm overwriting your current application and first creates a backup, if you do. It then collects all source-files in the Source-Directory and re-inserts them into the stub.

Have Fun!

Answer 6

There's a gotcha - VSS 6.0 can only accept MDB's using the add-in under a certain number of objects, which includes all local tables, queries, modules, and forms. Don't know the exact object limit.

To build our 10 year old prod floor app, which is huge, we are forced to combine 3 or 4 separate MDBs out of SS into one MDB , which complicates automated builds to the point we don't waste time doing it.

I think I'll try the script above to spew this MDb into SVN and simplify builds for everyone.

Answer 7

Hi,

i'm using the Access 2003 Add-in: Source Code Control. It works fine. One Problem are invalid characters like a ":".

I'm checkin in and out. Internly the Add-In do the same as the code up there, but with more tool support. I can see if an object is checked out and refresh the objects.

Answer 8

Olivers answer rocks, but the CurrentProject reference was not working for me. I ended up ripping the guts out of the middle of his export and replacing it with this, based on a similar solution by Arvin Meyer. Has the advantage of exporting Queries if you are using an mdb instead of an adp.

' Writes database componenets to a series of text files
' @author  Arvin Meyer
' @date    June 02, 1999
Function DocDatabase(oApp)
    Dim dbs 
    Dim cnt 
    Dim doc 
    Dim i
    Dim prefix
    Dim dctDelete
    Dim docName

    Const acQuery = 1

    Set dctDelete = CreateObject("Scripting.Dictionary")

    Set dbs = oApp.CurrentDb() ' use CurrentDb() to refresh Collections
    Set cnt = dbs.Containers("Forms")
    prefix = oApp.CurrentProject.Path & "\"
    For Each doc In cnt.Documents
        oApp.SaveAsText acForm, doc.Name, prefix & doc.Name & ".frm"
        dctDelete.Add "frm_" & doc.Name, acForm
    Next

    Set cnt = dbs.Containers("Reports")
    For Each doc In cnt.Documents
        oApp.SaveAsText acReport, doc.Name, prefix & doc.Name & ".rpt"
        dctDelete.Add "rpt_" & doc.Name, acReport
    Next

    Set cnt = dbs.Containers("Scripts")
    For Each doc In cnt.Documents
        oApp.SaveAsText acMacro, doc.Name, prefix & doc.Name & ".vbs"
        dctDelete.Add "vbs_" & doc.Name, acMacro
    Next

    Set cnt = dbs.Containers("Modules")
    For Each doc In cnt.Documents
        oApp.SaveAsText acModule, doc.Name, prefix & doc.Name & ".bas"
        dctDelete.Add "bas_" & doc.Name, acModule
    Next

    For i = 0 To dbs.QueryDefs.Count - 1
        oApp.SaveAsText acQuery, dbs.QueryDefs(i).Name, prefix & dbs.QueryDefs(i).Name & ".txt"
        dctDelete.Add "qry_" & dbs.QueryDefs(i).Name, acQuery
    Next

    WScript.Echo "deleting " & dctDelete.Count & " objects."
    For Each docName In dctDelete
        WScript.Echo "  " & Mid(docName, 5)
        oApp.DoCmd.DeleteObject dctDelete(docName), Mid(docName, 5)
    Next

    Set doc = Nothing
    Set cnt = Nothing
    Set dbs = Nothing
    Set dctDelete = Nothing

End Function

Answer 9

I need to implement this solution so we can check our Access App into Star Team and track our code changes to the VBA.

Every time I run this decompose.vbs script, I get this error:

"MICROSOFT OFFICE ACCESS CAN'T OPEN THE DATABASE BECAUSE IT IS MISSING OR OPENED EXLUSIVELY BY ANOTHER USER..."

Now, it creates a Source directory, even dumps the test_stub.mdb into C:\testFolder\Source\

Any idea on why this is happening?

I checked permissions on the folder and my user account has full control.

Answer 10

this script didn't work for us.

this did: http://www.accessmvp.com/Arvin/DocDatabase.txt

Answer 11

I worked on a small Access project a few months ago and found the same challenge. Instead of using Subversion, I ended up writing an add-in that functions as simple source control. When I stumbled on this question, I decided to give it away as a free add-in. It allows version control as well as multiple users to add in their changes to a central repository that is an Access Database. It isn't complicated but does store the changes in an Access Database, so it can be easily enhanced.

http://www.slbdevelopment.com/scaddin.aspx

Answer 12

I am apparently dense and can't figure out how to comment on the accepted answer, so my apologies. I am having an issue once the items are restored using the compose.vbs. I keep getting a 'File Not Found' error when I try to open forms or reports in the Access Project the files have been restored to. Has anyone else experience this? Thanks in advance for any help.

Answer 13

For those using Access 2010, SaveAsText is not a visible method in Intellisense but it appears to be a valid method, as Arvin Meyer's script mentioned earlier worked fine for me.

Interestingly, SaveAsAXL is new to 2010 and has the same signature as SaveAsText, though it appears it will only work with web databases, which require SharePoint Server 2010.

Answer 14

We had the same issue a while ago.

Our first try was a third-party tool which offers a proxy of the SourceSafe API for Subversion to be used with MS Access and VB 6. The Tool can be found here.

As we were not that satisfied with that tool we switched over to Visual SourceSafe and the VSS Acces Plugin.