Read all source code from an access database.

[beilstwh]

I am upgrading to a new version of an Retail system hosted on an oracle database. I want to use an access database that will connect to all our other access databases and dump all the source code from all our reports, forms, queries, and modules via an external database link into CLOBS in our oracle database. The insert into the oracle database is easy but I can't figure out how to access all the source code. I looked at all the system tables and couldn't find anything. I know that the data must be accessible because the access documentation can dump it into a word document. Any help would be appreciated.

We are currently using 2003 for all our external access databases. The vast majority of external accesses into the oracle system is from .NET

[ranman256]

The db itself IS a container for all the code. Just zip up the db and store it somewhere.
Deconstructing an entire db is wasted effort when the db has it all catagorized already.

[beilstwh]

I am NOT trying to store the contents or code of the database. I am trying to load the code into an oracle database that I will then use regular expressions against to look for all the changes that our new retail system is making in it's table changes. Storing it away in a zip file is worthless. Thanks for the suggestion though.

I have already scanned all our sql server databases and all oracle stored code in all our many databases and I only have the access databases left.

Since I can read word documents I guess I could use documenter and dump each database that way but that is clumsy as heck.

[John_G]

Not sure how useful this will be, but here is code I use to examine all the forms/reports programtically, and to extract the VBA code. What it does is save the form/report definition as a text file, then read the text file to determine object attributes, and to copy the VBA code to another file. Most of it you won't need, but I highlighted the important parts for you in red. Disregard the parts in blue, as they pertain only to the database and form the code is running in. This code is running in one database and examining forms in another, connected Access database. Code in green will have to be modified according to your file locations and names.

  '
  '  All the Forms
  '
  Forms![Main Menu]![progress box].Caption = "Extracting references in forms..."
  Forms![Main Menu].Repaint
  Open "d:\MS office\MS_Access\objectlevels_F.txt" For Output As #2
  For Each obj In ConnectApplication.CurrentProject.AllForms
    '
    ' Copy the form to the local database
    '
    DoCmd.TransferDatabase acImport, "Microsoft Access", ConnectDatabaseName, acForm, obj.Name, "_" & obj.Name
    referenced_Object = Nz(DLookup("object_id", "zobjects", "[object_name] = '" & obj.Name & "'"), -1)
    '
    ' Save as a text file
    '
    SaveAsText acForm, "_" & obj.Name, "d:\MS office\MS_Access\" & obj.Name & ".txt"
    DoCmd.DeleteObject acForm, "_" & obj.Name
    Print #2, "Form - " & obj.Name
    '
    ' Read the text file
    '
    Open "d:\MS office\MS_Access\" & obj.Name & ".txt" For Input As #1
    NestingLevel = 0
    Do
      Line Input #1, Textline
      If EOF(1) Then Exit Do
      If Left(Trim(Textline), 5) = "Begin" Or _
          InStr(strtran(Textline, " ", ""), "=Begin") > 0 Then
        NestingLevel = NestingLevel + 1
        Print #2, Format(NestingLevel, "00") & " " & Textline
      ElseIf Left(Trim(Textline), 3) = "End" Then
        Print #2, Format(NestingLevel, "00") & " " & Textline
        NestingLevel = NestingLevel - 1
      ElseIf Trim(Textline) = "CodeBehindForm" Then
        '
        ' Copy all the code to a temporary file
        '
        Open "d:\MS office\MS_Access\module.txt" For Output As #3
        Do
          Line Input #1, Textline
          If EOF(1) Then Exit Do
          Print #3, Textline
        Loop
        Close #3
        ProcCount = AllProcs2("d:\MS office\MS_Access\module.txt", obj.Name, 1, True)
        Exit Do
      Else
        Print #2, Format(NestingLevel, "00") & " " & Textline
      End If
    Loop
    Close #1
    '
    '  You will need some code in here to deal with the code text saved to "d:\MS office\MS_Access\module.txt"
    '
  Next   ' Work with the next form
  Close

The procedure for reports is almost identical.

[beilstwh]

Not sure how useful this will be, but here is code I use to examine all the forms/reports programtically, and to extract the VBA code. What it does is save the form/report definition as a text file, then read the text file to determine object attributes, and to copy the VBA code to another file. Most of it you won't need, but I highlighted the important parts for you in red. Disregard the parts in blue, as they pertain only to the database and form the code is running in. This code is running in one database and examining forms in another, connected Access database. Code in green will have to be modified according to your file locations and names.

  '
  '  All the Forms
  '
  Forms![Main Menu]![progress box].Caption = "Extracting references in forms..."
  Forms![Main Menu].Repaint
  Open "d:\MS office\MS_Access\objectlevels_F.txt" For Output As #2
  For Each obj In ConnectApplication.CurrentProject.AllForms
    '
    ' Copy the form to the local database
    '
    DoCmd.TransferDatabase acImport, "Microsoft Access", ConnectDatabaseName, acForm, obj.Name, "_" & obj.Name
    referenced_Object = Nz(DLookup("object_id", "zobjects", "[object_name] = '" & obj.Name & "'"), -1)
    '
    ' Save as a text file
    '
    SaveAsText acForm, "_" & obj.Name, "d:\MS office\MS_Access\" & obj.Name & ".txt"
    DoCmd.DeleteObject acForm, "_" & obj.Name
    Print #2, "Form - " & obj.Name
    '
    ' Read the text file
    '
    Open "d:\MS office\MS_Access\" & obj.Name & ".txt" For Input As #1
    NestingLevel = 0
    Do
      Line Input #1, Textline
      If EOF(1) Then Exit Do
      If Left(Trim(Textline), 5) = "Begin" Or _
          InStr(strtran(Textline, " ", ""), "=Begin") > 0 Then
        NestingLevel = NestingLevel + 1
        Print #2, Format(NestingLevel, "00") & " " & Textline
      ElseIf Left(Trim(Textline), 3) = "End" Then
        Print #2, Format(NestingLevel, "00") & " " & Textline
        NestingLevel = NestingLevel - 1
      ElseIf Trim(Textline) = "CodeBehindForm" Then
        '
        ' Copy all the code to a temporary file
        '
        Open "d:\MS office\MS_Access\module.txt" For Output As #3
        Do
          Line Input #1, Textline
          If EOF(1) Then Exit Do
          Print #3, Textline
        Loop
        Close #3
        ProcCount = AllProcs2("d:\MS office\MS_Access\module.txt", obj.Name, 1, True)
        Exit Do
      Else
        Print #2, Format(NestingLevel, "00") & " " & Textline
      End If
    Loop
    Close #1
    '
    '  You will need some code in here to deal with the code text saved to "d:\MS office\MS_Access\module.txt"
    '
  Next   ' Work with the next form
  Close

The procedure for reports is almost identical.

This is exactly what I was looking for. Thank you very, very much.