Get all source code in mdb file (msaccess) using C#/VBScript

Question

I would like to get all the source code from an Access database (forms, modules) without using Office interops.

I liked the AllCodeToDesktop() function found here. However that runs from inside the mdb file.

What would a VBScript file be that loads the mdb file and runs that function on it?

Answer 1

I would like to get all the source code from an access database (forms, modules) - without using office interops

Microsoft Office is required. You might as well paste your function into the MDB and run it from there.

Answer 2

If it is for a repository, you may want to look at Visual SourceSafe and get the Access VSS addin (the link is for Access 2003, have not tried other versions) http://www.microsoft.com/downloads/details.aspx?familyid=2ea45ff4-a916-48c5-8f84-44b91fa774bc&displaylang=en

I am guessing that Access addin will expose a COM interface that you could extract the source reasonably cleanly into your C# app if you wanted to go that route. In either case, as Robert noted, Office is still required.

Answer 3

To save a lot of time, is there any reason why you don’t use the built in documenter?

Tools->analyze->documenter

The above has the ability to produce a report that has all code in forms, reports, modules and class modules. This report can be send to word or even saved as a text file.

I only mentioning this to save you time and effort. There not a need to write any code here.

more notes: I don’t think you can do this external. You can use ms-access as com object, and use code to pull out the code as text, but you need access installed on that computer.

There is two approaches, one is to use the saveastext command. This will export any form, report, code etc as a text file (this what the source code add-ins all use).

The 2nd approach is to write automation code. Here is code in native access (VBA), but you just as well write this code in any language that supports com objects. The code will look like:

  Dim db               As Database
  Dim Doc              As Document
  Dim mdl              As Module
  Dim lngCount         As Long
  Dim strForm          As String
  Dim strOneLine       As String
  Dim sPtr             As Integer

  Set db = CurrentDb()
  ' count module LOC

  For Each Doc In db.Containers("Modules").Documents
      DoCmd.OpenModule Doc.Name

      Set mdl = Modules(Doc.Name)

      lngCount = lngCount + mdl.CountOfLines
      For i = 1 To lngCount
         strOneLine = mdl.Lines(i, 1)
         Debug.Print strOneLine
      Next i

      Set mdl = Nothing
      DoCmd.Close acModule, Doc.Name

  Next Doc

The above code can be easliy modifed to seach, or export the text. I think using saveastext is less code however, but then you get the forms definition etc, and that likely not what you want.

Answer 4

I am a lowly Access programmer and can't say that I understand your question.

So far as I can tell, to get what you want, you're going to have to automate Access via COM -- there is no other way. The routines to do this are version-agnostic, and will work in all versions of Access from at least 97, as long as the version of Access you're automating is forward-compatible with the files you're examining (i.e., A2000 will be unable to examine an ACCDB and may (or may not) give unreliable results for A2002, A2003 and A2007 MDBs).

The key is using Application.SaveAsText to save all your code-bearing objects. This would mean you'd want to loop through all the modules, forms and reports and for the ones with code modules, output with SaveAsText. For forms and reports you may want to check the HasModule property (though this is available only in design or form view, i.e., the form has to be open) if you want to skip all objects that lack modules.

If you don't care about A97 compatibility, it's much easier. You can use CurrentProject.AllForms, CurrentProject.AllReports and CurrentProject.AllModules. If you're not concerned with differentiating forms/reports with code modules and those without at this stage, you won't need to open them to check the HasModule property. Instead, you can review the resulting exported text file. If there is no module, the tag CodeBehindForm will be missing from the output file.

However, it's very common for forms to have a module but no actual code. Those modules will be listed in the SaveAsText output like this:

  CodeBehindForm
  Attribute VB_GlobalNameSpace = False
  Attribute VB_Creatable = True
  Attribute VB_PredeclaredId = True
  Attribute VB_Exposed = False
  Option Compare Database
  Option Explicit

The Option Explicit may or may not be there, and the Option Compare Database is technically not required, but 99% of modules created in Access will have one or both (only databases created in Access 2000 with default settings will lack Option Explicit). Code will start after the 4 attributes. If it's just the two OPTION statements, then there's no real code behind the form.

You should be able to do COM automation of Access from vbScript on any PC with a version of Access installed that is forwardly compatible with all the MDBs/ACCDBs you want to examine.