Searching through VBA accross multiple MDB's

[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?

@KitaYama I just updated a module three times and couldn't get the date to change in MSysObjects. When you look by turning on details in the Nav Pane, the modified date for ALL FOUR of the modules in this database changed to 8:12 today. I cannot explain why. Here is from a different db that...

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).

 

[cajulien]

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).

Because, as this thread's history states, using OpenDatabase runs the autostart logic i.e., the default form opens up and its on load/onOpen logic runs automatically and we definitely don't want this.

 

[theDBguy]

Because, as this thread's history states, using OpenDatabase runs the autostart logic i.e., the default form opens up and its on load/onOpen logic runs automatically and we definitely don't want this.

I could be wrong, but the thread history states the OpenCurrentDatabase runs the autostart logic - the OpenDatabase doesn't.

 

[Josef P.]

It is also about reading the code modules.
An open DAO.Database reference won't be of much use, will it?
Or is something else meant by OpenDatabase?

 

[theDBguy]

It is also about reading the code modules.
An open DAO.Database reference won't be of much use, will it?
Or is something else meant by OpenDatabase?

The new OP seems to only be concerned about dependencies, which doesn't include VBA. Maybe?

 

[cajulien]

It is also about reading the code modules.
An open DAO.Database reference won't be of much use, will it?
Or is something else meant by OpenDatabase?

Clearly I've created confusion with inadequately specific choice of words: apologies.
Yes this thread was about opening code modules of B from A, this is done and good, ty.
As a follow-up,
- from A I'd like to find out which of B's queries and tables are used as recordsource in any of B's forms, same idea for reports.
- from A I'd like to find out which of B's forms are used as SourceObject in any other of B's forms!subform control(s)

Outcome is a better version of existing Object Dependencies so I know which of B's object's are never used by any other B object and therefore just delete these useless objects. So far I've been able to determine the following by parsing SQL and VBA code modules:
- Queries referring to other queries or tables
- VBA code referring to queries, tables, forms, or reports

This is good but:
- some forms are not referred to any code but are used as SourceObjects is subform controls
- some queries/tables are never referred to in code (or queries for tables) but they used as recordsource in some forms

Thus my question: from A.accdb, can I determine the above 2 types of referrences i.e., Form/Report.RecordSource and SubFrom/Report.SourceObjects without launching B?

 

[Josef P.]

- some forms are not referred to any code but are used as SourceObjects is subform controls
- some queries/tables are never referred to in code (or queries for tables) but they used as recordsource in some forms

Thus my question: from A.accdb, can I determine the above 2 types of referrences i.e., Form/Report.RecordSource and SubFrom/Report.SourceObjects without launching B?

I don't think so, as I think you would need to traverse Form.Controls or use SaveAsText to create a searchable file.
Only the form names you would be able to retrieve via the msysObjects table. But that doesn't help you much.

Why is running the autostart mode actually so bad? Then a form is simply displayed. So what?
If necessary, you could disable these settings from the outside (for a copy of the frontend file).

BTW:
I understand the approach, with a large amount of front end files, to find the shared code modules.
But why do you have to run the unused elements in a file through the same mass evaluation as well?
I would rather look at this per file, because only there I can also set actions if needed.

 

[cajulien]

I don't think so, as I think you would need to traverse Form.Controls or use SaveAsText to create a searchable file.
Only the form names you would be able to retrieve via the msysObjects table. But that doesn't help you much.

Why is running the autostart mode actually so bad? Then a form is simply displayed. So what?
If necessary, you could disable these settings from the outside (for a copy of the frontend file).

BTW:
I understand the approach, with a large amount of front end files, to find the shared code modules.
But why do you have to run the unused elements in a file through the same mass evaluation as well?
I would rather look at this per file, because only there I can also set actions if needed.

"Why is running the autostart mode actually so bad?"
Because there's code that runs OnLoad or OnOpen of the autostart form, this code does a bunch of stuff we do not want to run.

As I stated, point is to develop an "Object Dependencies" like the current Access attempt but from A.accdb that confidently says
"this object found in B.mdb is never used anywhere in B, neither in its SQL, VBA, never referenced in Forms or Reports, neither in any subform or subreport."

As a result, we can systematically clean-up these old MDB files that contain a lot of garbage objects that no one ever dared to delete e.g., query1_old, zzzzQuery2, Form1_old, etc.

 

[theDBguy]

"Why is running the autostart mode actually so bad?"
Because there's code that runs OnLoad or OnOpen of the autostart form, this code does a bunch of stuff we do not want to run.

As I stated, point is to develop an "Object Dependencies" like the current Access attempt but from A.accdb that confidently says
"this object found in B.mdb is never used anywhere in B, neither in its SQL, VBA, never referenced in Forms or Reports, neither in any subform or subreport."

As a result, we can systematically clean-up these old MDB files that contain a lot of garbage objects that no one ever dared to delete e.g., query1_old, zzzzQuery2, Form1_old, etc.

Just thinking out loud... This could be a catch-22 situation. For example, both FormA and FormB are never used and can be deleted. However, the dependency checker code says FormA depends on FormB and FormB also depends on FormA. By the result of the check alone, you won't delete either form.

 

[Josef P.]

As I stated, point is to develop an "Object Dependencies" like the current Access attempt but from A.accdb that confidently says
"this object found in B.mdb is never used anywhere in B, neither in its SQL, VBA, never referenced in Forms or Reports, neither in any subform or subreport."

Why from A for the closed B?
Why not use A from opend B? (e. g. A is called as an Access Add-In with Application.Run ...)

But anyway:
Have you already tested if you can start the frontend files automatically by holding down the shift key?
.. Colin (@isladogs) does it this way in his Database Analyzer.

 

[isladogs]

My database analyzer (which @Josef P. just mentioned) gives detailed information about many things including all object dependencies without directly opening the external database or running its code. See my article

Database Analyzer Pro

The professional version of this utility allows you to quickly analyze the contents of any Access database. Features include view and analyze database objects, object metadata and get object sizes. You can view table data and table design. Also view table fields and field properties. View query...

www.isladogs.co.uk

 

[Josef P.]

without directly opening the external database

An Access instance is already opened with the file to be checked. It is just not visible.

I would try it this way:

keybd_event VK_SHIFT, 0, KEYEVENTF_KEYDOWN, 0
Set app = CreateObject(PathToAccdbToCheck)
keybd_event VK_SHIFT, 0, KEYEVENTF_KEYUP, 0

 

[isladogs]

An Access instance is already opened with the file to be checked. It is just not visible.

Correct. The app being tested is run in the background without being directly opened

My shift bypass code is basically the same as you've written

  ' Simulate pressing the Shift key
730         keybd_event vbKeyShift, 0, 0, 0

...followed by code to open the app in the background with or without a password

...
   ' Simulate releasing the Shift key
1130      keybd_event vbKeyShift, 0, 2, 0