Extracting Source Code from an MS Access DB

Question

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.

See also http://stackoverflow.com/questions/247292/working-with-multiple-programmers-on-ms-access — Yarik, Nov 03 '08 at 05:29
You might also want to take a look at his Q&A: https://stackoverflow.com/questions/247292/working-with-multiple-programmers-on-ms-access — Yarik, Nov 03 '08 at 05:30

score 1 · Answer 1 · answered Oct 13 '08 at 14:44

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

answered Oct 13 '08 at 14:44

Mitch Wheat

295,962
43
465
541

Hi Mitch, Sorry, I should have mentioned that. Our Source Control is SVN internally, I have looked at the VSS tool, but it really needs to be scriptable to a folder so I can commit it to SVN. Thanks for the answer though :-) – evilhomer Oct 13 '08 at 14:46
As a last resort, could you put all your Access development into VSS? – Mitch Wheat Oct 13 '08 at 14:48
VSS is probably better, but if it's not possible, The URL Mitch provides is your best bet. You'd use .SaveAsText to write the definitions of the Forms/Reports/Modules out to the file system where they can be managed by SVN. And it could be done from a VBScript, using COM automation. – David-W-Fenton Oct 13 '08 at 21:05
Before resorting to ad-hoc scripting, I would still try to find an MSSCCI adapter for SVN and see if it can work with MS Access. For example, I would take a look at http://www.daveswebsite.com/software/tamtamsvn/index.shtml and whatever else you can find by googling for MSSCCI and SVN/Subversion. – Yarik Nov 03 '08 at 05:27

score 1 · Answer 2 · answered Oct 13 '08 at 14:57

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

If you're running this in Access, it seems to me that it would much more sense to walk through the Forms, Reports and Modules collections and use Application.SaveAsText to write the code (and full form definitions, in fact) out to the file system. This would elminate the need for the FSO. — David-W-Fenton, Oct 13 '08 at 21:03
It can be convenient to write out all the code to one file and without having to walk though each collection. The resulting text can be used to compare the code from two possibly identical databases/front-ends. — Fionnuala, Oct 14 '08 at 21:47

score 1 · Answer 3 · answered Dec 22 '08 at 09:01

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.

Extracting Source Code from an MS Access DB

3 Answers3

Linked