Enumerating all Forms/Modules/Classes etc in an Access Database (1 Viewer)

[tfurnivall]

Hi there

I have a need to enumerate all the Tables/Forms/Modules/Classes etc in an Access Database. At present I'm scanning MSysObjects, but I was wondering if there were an easier way. Using Tables as an example the (pseudo-)code looks like this:

Get all MSysObjects where Type = <Tabletype>  ' An Access constant, value = 1
Strip off all 'Msys' and 'f_' entries                          ' Not needed for my purposes
Look in the TableDefs Collection and process the corresponding TableDef

Not pretty, perhaps, but it works!

My question is - the UserForms collection is only those forms that have been loaded. When I am running this procedure, I'd far rather not have to load and show each form in turn. Quite apart from the overhead, it would be visually very distracting! Is there an easier way to get at ALL the forms, not just the loaded ones?

Thanks,

Tony

PS To avoid religious debates - I just wanna do this - it's so that I am not tied to Access in the future!

 

[MarkK]

I think MSysObjects is your best bet. The thing about Access is that it's a hodgepodge, like Form and Report objects are provided by Access, TableDef and QueryDef objects are provided by DAO, and Modules and UserForms are provided by VBA. It's a little bit of magic really that it's wrapped up as effectively as it is into one product called Access.

 

[tfurnivall]

Well - 6 months seems a like a long enough time for people to forget about this, so let's see if we can whack the sleeping giants!

I'm trying to get access (sorry about that ;-) to forms, modules and tables in another database. I can list them, but now I need to gain access to, for example, the controls in a form.

I thought that creating a new instance of Access might allow me to do this, but it seems that there is a limitation to how much automation MS will allow, and interacting with another instance of Access is pretty limited.

Here's the skeleton I am using (DBTool). You'll need to run the DBTool procedure in the main module.

What I would like to do is to be able to capture each form as a 'loaded' form, so I can get access to all the controls, etc.

Any thoughts?

Tony

 

[mdlueck]

My question is - the UserForms collection is only those forms that have been loaded. When I am running this procedure, I'd far rather not have to load and show each form in turn. Quite apart from the overhead, it would be visually very distracting! Is there an easier way to get at ALL the forms, not just the loaded ones?

Have you seen:

'Purpose: Count the number of lines of code in your database.
'Author: Allen Browne (allen@allenbrowne.com)
'Release: 26 November 2007
'Copyright: None. You may use this and modify it for any database you write.
' All we ask is that you acknowledge the source (leave these comments in your code.)
'Documentation: http://allenbrowne.com/vba-CountLines.html

    For Each accObj In [COLOR=Blue][B]CurrentProject.AllForms[/B][/COLOR]
        strDoc = accObj.Name
        bWasOpen = accObj.IsLoaded
        If Not bWasOpen Then
            DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
        End If
        If Forms(strDoc).HasModule Then
            lngObjectCount = lngObjectCount + 1&
            lngLineCount = lngLineCount + GetModuleLines("Form_" & strDoc, False, iVerboseLevel)
        End If
        If Not bWasOpen Then
            DoCmd.Close acForm, strDoc, acSaveNo
        End If
    Next

 

[tfurnivall]

]We have some progress!

Thanks, Michael for your pointer. I have added it to my DBTool template, and it now allows me to manipulate a form in the Target database from within the Host database. (See attached DB). The critical code is:

Public Sub ListTargetForms()

Dim obj As AccessObject
Dim TargetForm As Form
Dim LongString As String

LongString = "Forms for " & TargetDBName & ":" & vbCrLf & vbCrLf

'   The following code gets TargetForm into a state where it can
'   be manipulated by this database's code!
For Each obj In Target.CurrentProject.AllForms
    LongString = LongString & obj.Name & vbCrLf
>>  Target.DoCmd.OpenForm obj.Name, acNormal
>>  Set TargetForm = Target.Forms(obj.Name)
    With TargetForm
         Debug.Print .Name, .Controls.Count
    End With
>>  Target.DoCmd.Close acForm, obj.Name, acSaveNo
Next obj

MsgBox LongString, vbOKOnly, "List of forms in " & TargetDBName

End Sub

(Sorry about the 'invalid syntax' of the >> markers! The significant thing was declaring a second instance of Access, and using that instance for the DoCmd. This evidently makes the Target database's object available to the code in this database. Exactly what I wanted.

I'll keep working and see if I can get to a production lab version.

Thanks, again Michael!

Tony

 

[mdlueck]

You are most welcome, Tony.

 

[Estuardo]

G'd morning!
In addition to what Michael suggested, i guess you may want to take a look at my "MS Access Automation" document.
G'd luck