Searching through VBA accross multiple MDB's

[cajulien]

Hi folks, been trying to figure this out for hours now...too many dead leads, maybe one of you can help.

We've a large number of old MDB files, each with a bunch of modules. We're trying to save all the VBA code across all MDB's modules to a single ACCDB table so we can search through it for strings e.g., references to specific folders or files.

From a new ACCDB I'm crawling a set of folders and their subfolders to find all the *.mdb's
For each mdb I can loop through the modules and get their names, but I can't seem to be able to retrieve their VBA.

To illustrate, assume A.accdb finds the following:
B.mdb: has modules W and X
C.mdb: has modules Y and Z

We're trying to put W, X, Y, and Z's VBA into a long Text field in a table in A.accdb so we can search using Instr(...) across all MDB's and their modules.
We know this won't be efficient text searching but it's certainly more efficient than opening and searching through each MDB one at a time: assume we're talking hundreds of MDB's.

Thanks for your help folks

 

[theDBguy]

Several possible approaches to include adding references to the MDB files or using SaveAsText to get a bunch of Text files you can combine and search through.

 

[cajulien]

"modify the code to run from an external database"

That's the problem guys: Application.SaveAsText acModule...is looking for module name in A.accdb, but the module name is found in B.mdb that is opened programmatically from A.

I'd need to be able to do something like the following from A.accdb:

Set db = OpenDatabase("B.mdb") 'not the current db
db.Application.SaveAsText acModule, ModuleNameInB, TargetFilePath

Am I missing something obvious or have you guys ever actually done this from an external database?

 

[theDBguy]

"modify the code to run from an external database"

That's the problem guys: Application.SaveAsText acModule...is looking for module name in A.accdb, but the module name is found in B.mdb that is opened programmatically from A.

I'd need to be able to do something like the following from A.accdb:

Set db = OpenDatabase("B.mdb") 'not the current db
db.Application.SaveAsText acModule, ModuleNameInB, TargetFilePath

Am I missing something obvious or have you guys ever actually done this from an external database?

You could try using VBS. Check this out.

Putting It All Together

I expanded on the original decompose.vbs script. The code below is the culmination of more than 50 individual changes and tweaks over 10+ years.

nolongerset.com

 

[cajulien]

You could try using VBS. Check this out.

Putting It All Together

I expanded on the original decompose.vbs script. The code below is the culmination of more than 50 individual changes and tweaks over 10+ years.

nolongerset.com

Gosh, thanks DBguy, I was hoping for a pure Access VBA solution but I'm starting to think this just isn't possible...

 

[ebs17]

Dim db  As DAO.Database

With db, a DAO reference to the database file is to be created in a visible manner.

For methods on the access object (application) such as DoCmd, SaveAsText, etc., you must also create a reference to the application object of the DB file.

Dim oAcc As Access.Application
Set oAcc = CreateObject("Access.Application")
With oAcc
    .OpenCurrentDatabase "Z:\...\Second.accdb"

   ' do something

    .CloseCurrentDatabase
End With
oAcc.Quit
Set oAcc = Nothing

 

[cajulien]

Ho...now that's new and promising...let me try that. Thanks so much @ebs17

 

[cajulien]

Oh noooo!!!
.OpenCurrentDatabase actually opens the second.MDB as if a user did, which then triggers all the autostart logic...we definitely DON'T want to do that.

Is there a way to OpenCurrentDatabase with a "Press SHIFT" option so nothing runs on its own?

On the plus side the Module's code did get exported to TXT as intended though...guess that's a forward movement...

 

[cajulien]

Am reading 20+ year old posts about this issue...seems there's no way around preventing the autorun from launching on OpenCurrentDatabase except using Windows API to pretend like "user" is pressing SHIFT.

>> Open other db with vba without running Autoexec

Hi, I want to use vba to open another database. When I do, the autoexec in the other database is triggered. How can I open this other database without running the autoexec in the other database? Any ideas or suggestions appreciated :-) Many thanks Jonathan

www.pcreview.co.uk

That's so ugly I'll have to think about how badly we need this...thanks for your help folks!

 

[theDBguy]

Am reading 20+ year old posts about this issue...seems there's no way around preventing the autorun from launching on OpenCurrentDatabase except using Windows API to pretend like "user" is pressing SHIFT.

>> Open other db with vba without running Autoexec

Hi, I want to use vba to open another database. When I do, the autoexec in the other database is triggered. How can I open this other database without running the autoexec in the other database? Any ideas or suggestions appreciated :-) Many thanks Jonathan

www.pcreview.co.uk

That's so ugly I'll have to think about how badly we need this...thanks for your help folks!

Besides OpenCurrentDatabase, there's also OpenDatabase, but I'm not sure if it does what you want.

 

[theDBguy]

@theDBguy Looks like the procedure takes three arguments. I can't figure out what the third one is:

' Usage:
' CScript decompose.vbs <input file> <path> <stubname>

"stubname" does not have any meaning to me. I tried just a string but that didn't work. I also don't see any place in the code where "stubname" is referenced. Do you know what is supposed to be passed here?

@Pat Hartman We'll probably have to ask Mike about that. I believe it's a leftover from his earlier/original code on the topic. He may have forgotten to remove it. Just my guess...

 

[cajulien]

To have two databases open, you need two objects.

Set ThisDB = CurrentDb()
Set ThatDB = DBEngine.Workspaces(0).OpenDatabase(strDatabase) ''''get strDatabase from form control or prompt.

@Pat Hartman : not sure where we are on this one...are you adding something to my original question or is this something different now?
From CurrentDb I'm already opening ThatDb in the default (0) workspace, problem is that I can't access ThatDb.mdb's module's VBA code, and I can't SaveToText without OpenCurrentDatabase

 

[theDBguy]

Well, the code won't work without it.

I could have sworn it worked for me when I gave it a try before. In the VBS code, there is this section.

    Dim sStubMDBFilename
    If (WScript.Arguments.Count < 3) then
        sStubMDBFilename = sExportpath & myName & "_stub." & myType
    Else
        sStubMDBFileName = WScript.Arguments(2)
    End If

I believe that's where <stubname> is used.

 

[theDBguy]

The script seems to need three arguments but I can't figure out how the third is used or what value I need to supply.

Hi @Pat Hartman

I just gave it a try and all I did was pass one argument (the name of the ACCDB file), and it was able to extract all the modules from it.

It created a "stub" file which was basically the same ACCDB file with all the objects removed after the export.

 

[isladogs]

@cajulien I think I was replying to theDBGuy.

I agree. I have not been able to get the export to text using a reference to a database other than the one where the code is running. I tried this numerous times in the past and gave it another go today. Just doesn't work.

It certainly does work. Here is one approach which I use regularly...

1. Link to the MSysObjects table in the external database (MSysObjectsEXT) entering the password if necessary

  'link to external MSysObjects table
     DoCmd.TransferDatabase acLink, "Microsoft Access", strFilePath, acTable, "MSysObjects", "MSysObjectsEXT"

2. Grab the connect string from the local MSysObjects table and from that the password where appropriate so you don't need to enter it again when you open the external database

        'get connection string from record in local MSysObjects
GetConnectPwd:
            strConnect = Nz(DLookup("Connect", "MSysObjects", "Database = '" & strFilePath & "'"), "")
                      
             If strConnect <> "" Then
               Set ExtDb = DBEngine.OpenDatabase(strFilePath, False, False, strConnect)
                    If Nz(strPwd, "") = "" Then
                            'get PWD from Connect string 
                       strPwd = Mid(strConnect, InStr(strConnect, "=") + 1)
                       strPwd = Left(strPwd, Len(strPwd) - 1)

                     End If
             Else
                 Set ExtDb = DBEngine.OpenDatabase(strFilePath, False)
                 Me.txtPwd = "No"
             End If

3. Set a reference to the external database

       Set appAcc = New Access.Application

      If strConnect <> "" Then
          appAcc.OpenCurrentDatabase strFilePath, False, strPwd
       Else
          appAcc.OpenCurrentDatabase strFilePath, False
        End If

4. Save any external database object to a text file

 appAcc.SaveAsText ObjectTypeID, ObjName, strTargetFile

For example:

 appAcc.SaveAsText acModule, "modVBE", "G:\MyFiles\ExampleDatabases\DatabaseAnalyzerPro\SavedObjects\Module_modVBE.txt"

 

[Josef P.]

Is there a way to OpenCurrentDatabase with a "Press SHIFT" option so nothing runs on its own?

It works also without OpenCurrentDatabase:
Load mdb/accdb as reference. If there is a name conflict, don't get confused, it is only necessary that the respective VBProject is visible in the project explorer. Then find out the appropriate VbProject and via VbComponents you have access to the code modules.

Private Sub Test()

   Dim FilePath As String

   FilePath = CurrentProject.Path & "\B.mdb"

On Error Resume Next
   Application.References.AddFromFile FilePath
   If Err.Number <> 0 Then ' Err 32813 is ok
      Debug.Print Err.Number, Err.Description
      Err.Clear
   End If
On Error GoTo 0

   DoSomethingWithVBP FindVbProject(FilePath)

End Sub

Private Function FindVbProject(ByVal FilePath2Check As String) As VBProject
   Dim i As Long
   For i = 1 To VBE.VBProjects.Count
      If VBE.VBProjects(i).FileName = FilePath2Check Then
      ' compare UNC path if files are in a share
         Set FindVbProject = VBE.VBProjects(i)
         Exit For
      End If
   Next
End Function

Private Sub DoSomethingWithVBP(ByVal vbp As VBProject)

   Dim vbc As VBComponent

   For Each vbc In vbp.VBComponents
      Debug.Print vbc.Name
   Next

End Sub

 

[cajulien]

It works also without OpenCurrentDatabase:
Load mdb/accdb as reference. If there is a name conflict, don't get confused, it is only necessary that the respective VBProject is visible in the project explorer. Then find out the appropriate VbProject and via VbComponents you have access to the code modules.

Private Sub Test()

   Dim FilePath As String

   FilePath = CurrentProject.Path & "\B.mdb"

On Error Resume Next
   Application.References.AddFromFile FilePath
   If Err.Number <> 0 Then ' Err 32813 is ok
      Debug.Print Err.Number, Err.Description
      Err.Clear
   End If
On Error GoTo 0

   DoSomethingWithVBP FindVbProject(FilePath)

End Sub

Private Function FindVbProject(ByVal FilePath2Check As String) As VBProject
   Dim i As Long
   For i = 1 To VBE.VBProjects.Count
      If VBE.VBProjects(i).FileName = FilePath2Check Then
      ' compare UNC path if files are in a share
         Set FindVbProject = VBE.VBProjects(i)
         Exit For
      End If
   Next
End Function

Private Sub DoSomethingWithVBP(ByVal vbp As VBProject)

   Dim vbc As VBComponent

   For Each vbc In vbp.VBComponents
      Debug.Print vbc.Name
   Next

End Sub

That's it!!! That's finally what I was looking for. Thanks so much @Josef P.
The short of it:
Add reference from file
Loop through VB Projects
Find the one you just referenced
Loop through VB components of the Vb Project
Get the name, type, and full code i.e., codeModuleObj.Lines(1, codeModuleObj.CountOfLines)

Remove the reference

Clean and tidy...thanks so much folks, this community is full of generous experts as usual

 

[MajP]

I rolled up a lot of this functionality into a class to make it a lot easier. You may find this helpful. I demo looping a project pulled in as a reference.

How to find unused object/items?

Doesn't Peter Cole's 32bit to 64bit Conversion program (Access Europe) do something similar? It trawls through the code and locates where changes should be made. Which is similar but not identcal ot this requirement. Is it possible to repeat what Peter is doing, with amendments?

www.access-programmers.co.uk

 

[cajulien]

Could be I should create a new thread for this one but it's related to the broader topic of documenting Access apps and finding their unused objects.

Same context as above: A.accdb needs to access an old B.mdb and document it. At this point I'm trying to determine object dependencies of B.mdb i.e., figure out which of B's objects are useless.

Specifically, from A.accdb VBA I want to retrieve :
- B.mdb's form RecordSource property values: this will allow me to know which of B's querys and tables are referenced by B's forms
- Same as above for reports
- For each of B.mdb's forms, find their subform control(s), if any, retrieve its SourceObject property value: this tells me which forms refer to which other ones.
- Same as above for reports and their sub-reports.

I get that I could easily do this my adding code to B.mdb but the whole point is not to modify or run these old MDB files i.e., can't use OpenDatabase.

Thanks so much for your relevant suggestions guys.

 

[CJ_London]

Why can't you use opendatabase? I'm not aware of any method of obtaining the data you want without using opendatabase.

If the mdb is too old a version to be opened in your version, you need to get the mdb updated to a later version (2000 or 2003).