Object Definitions from External Db

[Phil]

My ultimate goal is to be able to completely map out an Access db: tables,
queries, forms, reports, macros, modules with all the included
controls/properties/VBA code. I can dig deep into these top 6 objects within
a db but this requires the code to be included in the db I am mapping. That
would defeat the whole purpose. The following code does work for getting a
list of forms from another db (I can write similar code for reports, tables,
etc.).

*****************
Public Sub GetFormInfo(dbFullName As String, tblForm As String)
' dbFullName = the name of the db being mapped
' tblForm = the name of a table with a text field "FormName"

Dim db As Database, dbBrowse As Database
Dim rs As Recordset

Dim objCon As Container
Dim objDoc As Document

Set db = CurrentDb()
Set rs = db.OpenRecordset(tblForm, dbOpenDynaset)

Set dbBrowse = OpenDatabase(dbFullName)

With dbBrowse
For Each objCon In .Containers
If objCon.Name = "Forms" Then
For Each objDoc In objCon.Documents
rs.AddNew
rs("FormName") = objDoc.Name
rs.Update
Next
End If
Next

.Close
End With

rs.Close
db.Close
End Sub
*****************

I'd appreciate some success stories (with code), pointers in the right
direction, etc.

 

[Marshall Barton]

My ultimate goal is to be able to completely map out an Access db: tables,
queries, forms, reports, macros, modules with all the included
controls/properties/VBA code. I can dig deep into these top 6 objects within
a db but this requires the code to be included in the db I am mapping. That
would defeat the whole purpose. The following code does work for getting a
list of forms from another db (I can write similar code for reports, tables,
etc.).

*****************
Public Sub GetFormInfo(dbFullName As String, tblForm As String)
' dbFullName = the name of the db being mapped
' tblForm = the name of a table with a text field "FormName"

Dim db As Database, dbBrowse As Database
Dim rs As Recordset

Dim objCon As Container
Dim objDoc As Document

Set db = CurrentDb()
Set rs = db.OpenRecordset(tblForm, dbOpenDynaset)

Set dbBrowse = OpenDatabase(dbFullName)

With dbBrowse
For Each objCon In .Containers
If objCon.Name = "Forms" Then
For Each objDoc In objCon.Documents
rs.AddNew
rs("FormName") = objDoc.Name
rs.Update
Next
End If
Next

.Close
End With

rs.Close
db.Close
End Sub
*****************

I'd appreciate some success stories (with code), pointers in the right
direction, etc.

Modules are in the Modules container.

It's pretty much the same for tables and queries except they
are in their own collections:

For Each tdf In .TableDefs
For Each fld in tdf.Fields

same for QueryDefs

Macros are in the Scripts container, but I don't know if you
can get much more than the Name.

Your idea is an excellent learning exercise, but if you have
other things to do and just want the information, take a
look at the Access ... tools at http://www.fmsinc.com/

 

[Phil]

Modules are in the Modules container.

It's pretty much the same for tables and queries except they
are in their own collections:

For Each tdf In .TableDefs
For Each fld in tdf.Fields

same for QueryDefs

Macros are in the Scripts container, but I don't know if you
can get much more than the Name.

Your idea is an excellent learning exercise, but if you have
other things to do and just want the information, take a
look at the Access ... tools at http://www.fmsinc.com/

Thanks for the pointer to FMS, but I'm still am intent on extracting the
info using VBA code (which I can fully control). I did find that I can get
all the info I want for tables & queries, however, I can only get lists of
forms, reports, macro & modules.

Hey MS Access: Gimme more!

 

[Marshall Barton]

Thanks for the pointer to FMS, but I'm still am intent on extracting the
info using VBA code (which I can fully control). I did find that I can get
all the info I want for tables & queries, however, I can only get lists of
forms, reports, macro & modules.

To get to the suff inside a form/report/module, you need to
open it in design view.

DoCmd.OpenForm objDoc.Name, acDesign
Set frm = Forms(objDoc.Name)
For Each ctl In frm.Controls
For Each prp In ctl.Properties
. . .
DoCmd.Close acForm, objDoc.Name, acSaveNo
Set frm = Nothing

Modules are a little different so check Help

 

[Phil]

This is basically the code I am trying to use. With the lines I have
commented out
it works fine to collect a list of the forms within another database.
However, it
thinks that the forms I am trying to open are in the current database and I
don't
know how to tell it otherwise. BTW, I have used this same code to
successfully to
collect all the info I wanted from the same database that the code resides in.
It defeats the purpose to have to include all the code and any tables,
queries, etc.
in every database that I might want to look at.

******
Dim db As Database, dbBrowse As Database
Dim rs As Recordset

Dim objCon As Container
Dim objDoc As Document

Set db = CurrentDb()
' tblForms is the name of a table defined to hold the info about
' the forms and controls in the separate database named in dbFullName
Set rs = db.OpenRecordset(tblForms, dbOpenDynaset)

Dim obj As AccessObject, currObj As Object
Dim ctl As Control

Set dbBrowse = OpenDatabase(dbFullName)

Set currObj = Application.CurrentProject

With dbBrowse
For Each objCon In .Containers
If objCon.Name = "Forms" Then
For Each objDoc In objCon.Documents
' DoCmd.OpenForm objDoc.Name, acDesign, , , , acHidden

' For Each ctl In .Form(objDoc.Name).Controls
rs.AddNew

rs("FormName") = objDoc.Name
' rs("ObjectName") = ctl.Name
' rs("ObjectType") = GetControlType(ctl)
' rs("Visible") = ctl.Visible
' rs("ControlSource") = Nz(ctl.ControlSource)
' rs("RowSource") = Nz(ctl.RowSource)
' rs("ControlTip") = Nz(ctl.ControlTipText)

rs.Update
' Next ctl

' DoCmd.Close acForm, objDoc.Name, acSaveNo
Next
End If
Next

.Close
End With

rs.Close
db.Close
******

 

[Phil]

With a bit of digging into Microsoft Support, some tips from another posting
that I happened to find, and just a smidge of trial & error, I did manage to
piece together much of what I wanted. Thanks for getting me started.

*****
Public Sub GetFormsInfo(dbFullName As String, tblForms As String)
' dbFullName = the mdb file (with full path) that is to be investigated
' tblForms = the table of results

Dim db As Database, dbBrowse As Database
Dim rs As Recordset

Dim appAccess As Object

Dim objCon As Container
Dim objDoc As Document
Dim Frm As Form
Dim propValue As String

On Error GoTo prop_err

Set db = CurrentDb()
Set rs = db.OpenRecordset(tblForms, dbOpenDynaset)

Dim obj As AccessObject, currObj As Object
Dim ctl As Control

Set dbBrowse = OpenDatabase(dbFullName)

Set currObj = Application.CurrentProject

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase dbFullName

With dbBrowse
For Each objCon In .Containers
If objCon.Name = "Forms" Then
For Each objDoc In objCon.Documents
appAccess.DoCmd.OpenForm objDoc.Name, acDesign, , , ,
acHidden
Set Frm = appAccess.Forms(objDoc.Name)

For Each ctl In Frm.Controls
rs.AddNew

rs("FormName") = objDoc.Name
rs("ObjectName") = ctl.Name
rs("ObjectType") = GetControlType(ctl)
rs("Visible") = ctl.Visible

' a roundabout way to safely get these 3 property values
' (some controls might not have some of these properties defined)
propValue = Nz(ctl.ControlSource)
rs("ControlSource") = propValue

propValue = Nz(ctl.RowSource)
rs("RowSource") = propValue

propValue = Nz(ctl.ControlTipText)
rs("ControlTip") = propValue

rs.Update
Next ctl

appAccess.DoCmd.Close acForm, objDoc.Name, acSaveNo
Next
End If
Next

.Close
End With

rs.Close
db.Close

Exit Sub

prop_err:
propValue = ""
Resume Next

End Sub
*****

 

[Marshall Barton]

RIght! Somehow, I lost track of the other database part of
your question. In that case you should use Automation as
you are doing.