How do you use version control with Access develop

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

I tried to help contribute to his answer by adding an export option for Queries within the access database. (With ample help from other SO answers)

Dim def
Set stream = fso.CreateTextFile(sExportpath & "\" & myName & ".queries.txt")
  For Each def In oApplication.CurrentDb.QueryDefs

    WScript.Echo "  Exporting Queries to Text..."
    stream.WriteLine("Name: " & def.Name)
    stream.WriteLine(def.SQL)
    stream.writeline "--------------------------"
    stream.writeline " "

  Next
stream.Close

Haven't be able to work that back into the 'compose' feature, but that's not what I need it to do right now.

Note: I also added ".txt" to each of the exported file names in decompose.vbs so that the source control would immediately show me the file diffs.

Hope that helps!

---

Answer 3

I'm using Oasis-Svn http://dev2dev.de/

I just can tell it has saved me at least once. My mdb was growing beyond 2 GB and that broke it. I could go back to an old version and import the Forms and just lost a day or so of work.

Answer 4

Resurrecting an old thread but this is a good one. I've implemented the two scripts (compose.vbs / decompose.vbs) for my own project and ran into a problem with old .mdb files:

It stalls when it gets to a form that includes the code:

NoSaveCTIWhenDisabled =1

Access says it has a problem and that's the end of the story. I ran some tests and played around trying to get around this issue and found this thread with a work around at the end:

Can't create database

Basically (in case the thread goes dead), you take the .mdb and do a "Save as" to the new .accdb format. Then the source safe or compose/decompose stuff will work. I also had to play around for 10 minutes to get the right command line syntax for the (de)compose scripts to work right so here's that info as well:

To compose (say your stuff is located in C:\SControl (create a sub folder named Source to store the extracted files):

'(to extract for importing to source control)
cscript compose.vbs database.accdb     

'(to rebuild from extracted files saved from an earlier date)
cscript decompose.vbs database.accdb C:\SControl\Source\

That's it!

The versions of Access where I've experienced the problem above include Access 2000-2003 ".mdb" databases and fixed the problem by saving them into the 2007-2010 ".accdb" formats prior to running the compose/decompose scripts. After the conversion the scripts work just fine!

Answer 5

this script didn't work for us.

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

Answer 6

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.