Extracting Source Code from an MS Access DB

Question

Hi, I have an Access DB that I would like to extract the source code from so I can put it into Source control.

I have tried to extract the data using the Primary Interop Assemblies(PIA), but I am getting issues as it is not picking up all of the modules and forms.

There are 140 Forms and Modules in the code(Don't ask, it's a legacy system I have inherited) but the PIA code is only picking up 91 of them.

Here is the code I am using.

using System;
using Microsoft.Office.Interop.Access;

namespace GetAccesSourceFiles
{
    class Program
    {
        static void Main(string[] args)
        {
            ApplicationClass appClass = new ApplicationClass();
            try
            {
                appClass.OpenCurrentDatabase("C:\\svn\\projects\\db.mdb",false,"");

                Console.WriteLine(appClass.Version);
                Console.WriteLine(appClass.Modules.Count.ToString());
                Console.WriteLine(appClass.Modules.Parent.ToString());

                int NumOfLines = 0;
                for (int i = 0; i < appClass.Modules.Count; i++)
                {
                    Console.WriteLine(appClass.Modules[i].Name + " : " + appClass.Modules[i].CountOfLines);
                    NumOfLines += appClass.Modules[i].CountOfLines;
                }

                Console.WriteLine("Number of Lines : " + NumOfLines);
                Console.ReadKey();
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message + "\r\n" +ex.StackTrace);
            }
            finally
            {
                appClass.CloseCurrentDatabase();
                appClass.Quit(AcQuitOption.acQuitSaveNone);
            }

        }
    }
}

Any suggestions on what that code might be missing? or on a product/tool out there that will do this for me?

Edit: I should also mention that this needs to script to disk, integration with VSS is not an option as our source system is SVN. Thanks.

Answer 1

There is a better way. You can use Visual Sourcesafe (and possibly other SCCs) to version control code and objects in place: see this MSDN article

Answer 2

This may help:

    Sub AllCodeToDesktop()
       'The reference for the FileSystemObject Object is Windows Script Host Object Model
       'but it not necessary to add the reference for this procedure.

       Dim fs As Object
       Dim f As Object
       Dim strMod As String
       Dim mdl As Object
       Dim i As Integer

       Set fs = CreateObject("Scripting.FileSystemObject")

       'Set up the file.
       Set f = fs.CreateTextFile(SpFolder("Desktop") & "\" _
         & Replace(CurrentProject.Name, ".", "") & ".txt")

       'For each component in the project ...
       For Each mdl In VBE.ActiveVBProject.VBComponents
           'using the count of lines ...
           i = VBE.ActiveVBProject.VBComponents(mdl.Name).CodeModule.CountOfLines
           'put the code in a string ...
           If VBE.ActiveVBProject.VBComponents(mdl.Name).codemodule.CountOfLines > 0 Then
              strMod = VBE.ActiveVBProject.VBComponents(mdl.Name).codemodule.Lines(1, i)
           End If
           'and then write it to a file, first marking the start with
           'some equal signs and the component name.
           f.writeline String(15, "=") & vbCrLf & mdl.Name _
               & vbCrLf & String(15, "=") & vbCrLf & strMod
       Next

       'Close eveything
       f.Close
       Set fs = Nothing
   End Sub

   Function SpFolder(SpName As String)
   'Special folders
       SpFolder = CreateObject("WScript.Shell").SpecialFolders(SpName)
   End Function

From: http://wiki.lessthandot.com/index.php/Code_and_Code_Windows

Answer 3

You might also want to take a look at his Q&A:

http://stackoverflow.com/questions/247292/working-with-multiple-programmers-on-ms-access

Answer 4

You could use the undocumented Application.SaveAsText and Application.LoadFromText functions. SaveAsText works on modules, forms, and reports; and when you save a form or report as text, its module code will appear at the bottom of the resulting text file.

You could write a routine that would save all of the non-data objects in your Access MDB (or ADP) as text, put it in a module, and just keep that module in a development version of your Access DB. Then you could run the routine and check in the dumped code in VSS.

It's probably not as elegant as the Visual SourceSafe method described by Mitch Wheat, but that depends on what you want to do with the source code. I tend to hang onto multiple versions of MDB's, and use this method to compare source code between them using diff tools such as WinMerge. It's good for porting functionality between branches of development.

It's also good for locating all references to fields or controls wherever they may be found. Viewing Access objects as textual definitions makes finding these references dead simple.