How to reference code in separate db

[Pat Hartman]

I'm working on my documentation tool and moving into unchartered waters. Up to now, I've been working with DAO objects and so having no trouble referencing them. Now I want to reference code and I found some useful code but it works for the current db. I need to figure out how to change the reference to a different db.

I need to access tables in the code db because that is where the logging is done and I also have a reference to a foreign db because those are the table properties I am documenting.

    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    db.Containers.Refresh

So, this gives me a ThisDB to reference anything in the db running the code and db to reference anything in the database I am analyzing.

The code I found uses this reference:

    Dim basModule   As VBComponent
    ....
    ....
        For Each basModule In Application.VBE.ActiveVBProject.VBComponents

I need to figure out how to change Application.VBE.ActiveVBProject to reference the "other" db rather than the current db so I can look at the code in THAT db. All I am doing for now is listing the module names and the lines of code. The sample code works great for "this" database.

Thanks for helping

 

[jdraw]

Pat,

Try this.

' ----------------------------------------------------------------
' Procedure Name: ViewAnotherDatabaseCode
' Purpose: Routine to open a second database and list its VBIDE components (modules)
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 22-May-23
' ----------------------------------------------------------------
Sub ViewAnotherDatabaseCode()
          Dim thisDb As DAO.Database
10        Set thisDb = CurrentDb
          Dim thatDB As Access.Application
        
          ' Open the that database
20        Set thatDB = New Access.Application
30        thatDB.OpenCurrentDatabase "C:\users\jp\documents\DEleteMTSubFolders.accdb"  ' Path to your "That Database" file
        
          ' Activate the Visual Basic Editor in the thatDB
40        thatDB.VBE.MainWindow.Visible = True
        
          ' Reference the ActiveVBProject in the thatDB
          Dim thatVBProject As VBIDE.VBProject
50        Set thatVBProject = thatDB.VBE.ActiveVBProject
        
        
          ' Iterate through the components in the that project:
          Dim component As VBIDE.VBComponent
60        For Each component In thatVBProject.VBComponents
             'List component names to immediate window

70            Debug.Print component.Name
              ' Whatever you need to do with the code components in the thatDB goes here

80        Next component
        
          ' Close the thatDB
90        thatDB.CloseCurrentDatabase
100       Set thatDB = Nothing
110       thisDb.Close
120       Set thisDb = Nothing
End Sub

 

[Pat Hartman]

Thanks Jack. That was quick

 

[Pat Hartman]

The solution works on trivial databases but not my production ones. They all have code that runs when they open. I don't want to automate the other db. I just want to examine its objects. The point is to be able to examine a bunch of databases in a folder so I can see what objects they contain and capture some details about them. The method I was using to look at tables/queries and other DAO objects didn't have to open the db so it could look at anything.

It's late. You gave me a start. I'll see if I can create something that doesn't need to actually open the database to look at the contents. Or, at least opens it but bypasses the form opening.

 

[561414]

This perhaps

Sub ListDbObjects()
    Dim db As DAO.Database
    Dim obj As Object

    Set db = DBEngine.OpenDatabase("some_file_path.accdb")

    For Each obj In db.Containers("modules").Documents
        Debug.Print obj.Name
    Next obj
'    Examine containers
'    Stop
    Set obj = Nothing
    db.Close
    Set db = Nothing
End Sub

Found a bunch of containers, like Tables, Forms, Modules, Relationships, Reports, etc. The db object also has a QueryDefs and a TableDefs collection in my tests, as well as properties, recordsets and a few extra info you might use, so it might work.

And extra stuff I found examining the objects:

Function ByPassStartUpForm(filepath As String) As String
    Dim db As DAO.Database
    Dim obj As Object

    'Open
    Set db = DBEngine.OpenDatabase(filepath)

    'Get
    ByPassStartUpForm = db.Properties.item("StartUpForm")

    'Set
    db.Properties.item("StartUpForm") = "(none)"

    'Close
    Set obj = Nothing
    db.Close
    Set db = Nothing
End Function

Function OpenAccessDB(ByVal path As String) As Access.Application
    Dim app As Access.Application
    Set app = New Access.Application
    app.OpenCurrentDatabase path
    Set OpenAccessDB = app
End Function


Sub ListLinesOfCode()
    Dim app As Access.Application
    Dim frm As String
    Dim path As String
    Dim item As Object
    path = "fullfilepath.accdb"

    'get form and bypass it
    frm = ByPassStartUpForm(path)

    'open and do stuff
    Set app = OpenAccessDB(path)

    'print counts of lines
    For Each item In app.VBE.ActiveVBProject.VBComponents
        Debug.Print item.Name, item.CodeModule.CountOfLines
    Next item

    app.DBEngine.Workspaces(0).Databases(0).Properties.item("StartUpForm") = frm

    'close
    app.CloseCurrentDatabase
    Set app = Nothing
End Sub

It is not optimized, but it gets the job done.

 

[ebs17]

The point is to be able to examine a bunch of databases in a folder so I can see what objects they contain and capture some details about them.

Source control systems use [Application.]SaveAsText and [Application.]LoadFromText to externally manage the definitions of objects.
Each object (form, report, module) has its own text file, and with a little practice you can learn something about the content and structure of these files.

With an up-to-date stock of these definition files, you are free in the analyses. Plain text files are easy to read.

 

[Josef P.]

What information do you need from the codemodules?
For anything more than the name, I think the application reference (or VBProject reference) is needed => open application.
Maybe it is enough to set a VBA reference to the file(s).

 

[isladogs]

If you just want a list of database objects and object types, link to MSysObjects in the other database.
Or use my utility app

View Database Objects & Metadata

This article includes an example app used to view all objects in a database together with their properties and metadata.

www.isladogs.co.uk

Or if you want to view the code in the external app, install it as a reference library. You will have full access to the code but not the objects

 

[jdraw]

The solution works on trivial databases but not my production ones. They all have code that runs when they open. I don't want to automate the other db. I just want to examine its objects. The point is to be able to examine a bunch of databases in a folder so I can see what objects they contain and capture some details about them. The method I was using to look at tables/queries and other DAO objects didn't have to open the db so it could look at anything.

It's late. You gave me a start. I'll see if I can create something that doesn't need to actually open the database to look at the contents. Or, at least opens it but bypasses the form opening.

Pat,
I focused on vbe because of your reference to components. I thought you were going for modules/procedures.

I have used SaveAsText and parsed the sources for various things as mentioned by ebs17.
This is the code that I used as my starting point.

Another was to use DIR to create a list/textfile of all *.accdb files,
eg. 'C:\>dir *.accdb /s/b >c:\users\jp\documents\Databases_CDrive.txt

then using each record that list use the MsysObjects to identify the various objects in each database as below.

 

[Pat Hartman]

As I said, this documentation application has been working for years to analyze tables/tablefields/indexes/queries/queryfields and their various properties. I have a bunch of reports so I can identify inconsistencies in data definitions, etc. I also dabbled in generating DDL and I can generate Access, T-SQL, and Sybase versions of DDL to make tables and add constraints. I use the table and query field cross references to determine how extensive a change might be. This only works well because I never use Select *. I always select the specific fields needed for the task and 99% of the time I use querydefs rather than embedded SQL.

I also have extensive backup capabilities that export everything except tables to text. The backup to text doesn't exist in all databases though so I can't use that as the source to examine for this stage of the tool's development. One of the goals of the project expansion is to find out which applications have my "standard" features. At the moment, for this new code, I am capturing only the name of the code module and the number of lines of code so I have a clue if the modules are different versions without actually having to compare the code.

The MSysObjects table is useful for finding a list of objects and in some cases their last update date. That is what I use to control my backup code. It is really too bad that it doesn't distinguish the update dates for individual modules. We've had this conversation also. Part of it revolved around finding a list of properties in the properties property for objects and there was no solution for that since the code simply listed the parent properties.

I was trying to use the containers the last time I looked at this but ran into problems with the code so I just commented it out and went back to paying work. I just resurrected the Forms code and I got it to work by dimming AO as DAO.Document but that just gives me the name, the DateCreated and LastUpdated ( For Each AO In ThatDB.Containers("Forms").Documents). There doesn't seem to be any way to go lower.

I think if I want to get the number of lines of code or the controls in a form, I might have to actually open the database so I'll also try the code that bypasses the startup form. It would be nice if there were a way to open it silently so it doesn't pop up in my face. I want it to work the way it does with Excel and Word. You never make the document visible unless you want to entertain the user and let him watch as you add stuff and format it But since having the document visible dramatically slows down the OLE, I just never make the objects visible.

Also, back to the earlier Access code, the .CloseCurrentDatabase method closes the database but not the Access window. I haven't tried this in a loop to process multiple files but this isn't going to work unless I can figure out how to close the extra instance of Access also.

 

[Josef P.]

By setting a reference, it runs without starting AutoExec or similar.
Code snippet

Dim VbaRef As Access.Reference
Dim FilePath As String

FilePath = ... ' path to accdb
Set VbaRef = Application.References.AddFromFile(FilePath)
DoSomethingWithVbProject VbeTools.FindVbProject(FilePath)
Application.References.Remove VbaRef

For the "real" application, I would create a temp file (accdb) and set the references in it.

 

[Pat Hartman]

Thanks. That sounds useful.