Export all VBA code for an MS Access database 1

[FarmboyEsq]

Hello,

Is there anyway to export all of the VBA code behind a MS Access 2007 database? (The code behind the forms is what I'm after.)

The File-> Export File only seems to want to export the form or code you are currently looking at.

Here's my real problem. I have a semi-large Access 2007 DB with tons of code I want to steal. It won't let me save the whole DB as an Access 2003 DB because of some of the features it employs. I'm working on a different database on another machine that has Access 2003. I would like to at least have a .txt file with all of my code in it from the 2007 database so I can copy and adapt it.

Thanks!

Stg

 

[lewds]

Hi...

Open the Visual Basic Editor

File > Print > Current Project

 

[CautionMP]

FarmboyEsq,
Give this a try. I use this in a stand alone database to document my projects in a somewhat automated fashion.

NOTE:Requires a reference to Microsoft Visual Basic for Applications Extensibility M.n in your VBA project (my computer has version 5.3).

Public Function DocumentModule(RootDirectory As String, ExportBas As Boolean, ExportText As Boolean)
'Make sure a root directory was specified
If Len(RootDirectory) = 0 Then
  Exit Function
End If
Dim appAccess As New Access.Application
Dim objProj As VBProject
Dim objComponent As VBComponent
Dim intFile As Integer
Dim strModule As String

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase ("[i][b]C:\your.mdb[/b][/i]")

Set objProj = appAccess.VBE.ActiveVBProject

For Each objComponent In objProj.VBComponents
  'Export the files as *.bas files
  If ExportBas Then
    objComponent.Export RootDirectory & "\" & objComponent.Name & ".bas"
  End If
  'Export the modules as *.txt files
  If ExportText Then
    intFile = FreeFile
    Open RootDirectory & "\" & objComponent.Name & ".txt" For Output As #intFile
    strModule = objComponent.CodeModule.Lines(1, objComponent.CodeModule.CountOfLines)
    Print #intFile, strModule
    Close #intFile
  End If
Next objComponent
'Clean up
Set objComponent = Nothing
Set objProj = Nothing
appAccess.CloseCurrentDatabase
Set appAccess = Nothing
End Function

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[CaptainD]

Nice post, I've always just cut and paste, never tried to do it by code.

Thanks

 

[Remou]

If you require form code, you can try this. No references are required.

Sub CallListFormMods()
Dim apAcc As Access.Application

Set apAcc = CreateObject("Access.Application")

strDB = "C:\Docs\Tek-Tips.mdb"

apAcc.OpenCurrentDatabase (strDB)

ListFormMods apAcc

apAcc.CloseCurrentDatabase
apAcc.Quit
Set apAcc = Nothing
End Sub

Sub ListFormMods(apAcc)
Dim frm As Object
Dim mdl As Module
Dim fs As Object
Dim f As Object
Dim strPath As String

Set fs = CreateObject("Scripting.FileSystemObject")
strPath = CurrentProject.Path

For Each frm In apAcc.Application.CurrentProject.AllForms
    apAcc.DoCmd.OpenForm frm.Name, acDesign, , , , acHidden
    
    If apAcc.Forms(frm.Name).HasModule Then
        Set mdl = apAcc.Forms(frm.Name).Module
        If mdl.CountOfLines > 0 Then
            Set f = fs.CreateTextFile(strPath & frm.Name & ".txt")
            f.write mdl.Lines(1, mdl.CountOfLines)
        End If
    End If
    
    apAcc.DoCmd.Close acForm, frm.Name
Next
End Sub

I use something similar for modules.

<...>
For i = 0 To (AcApp.Application.CurrentProject.AllModules.Count) - 1
    modname = AcApp.Application.CurrentProject.AllModules(i).Name
    AcApp.DoCmd.OpenModule modname
    Set mdl = AcApp.Modules(modname)
    If mdl.CountOfLines > 0 Then
       Set f = fs.CreateTextFile(strPath & modname & ".txt")
       f.write mdl.Lines(1, mdl.CountOfLines) & " "
    End If
    AcApp.DoCmd.Close acModule, modname
    Set mdl = Nothing
Next

Though I do not usually write to textfiles.

 

[FarmboyEsq]

Great Stuff -- Thanks!

Stg