Searching through VBA accross multiple MDB's

[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

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, form, report, macro and...

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