Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 18th, 2007, 05:22 PM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default Object Definitions in External Db

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.


-Phil-
__________________
      -Phil-
 
Old December 19th, 2007, 08:27 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Have you tried using the database analyzer?

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old December 19th, 2007, 10:28 AM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Do you mean the Documenter? That only shows info for the current db. Besides, I want to collect the info thru VBA code that I can fully control.

-Phil-
 
Old December 20th, 2007, 04:24 PM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
*****


-Phil-





Similar Threads
Thread Thread Starter Forum Replies Last Post
Open external DB in MS Access VB problem Derek_05 Access VBA 1 January 21st, 2006 03:51 AM
how to Insert data into external db method Access VBA 5 May 4th, 2005 10:59 AM
External db files to Excel, via VBA automation ?? cipher_nb Excel VBA 1 December 13th, 2004 04:56 AM
Using Serviced DB object thermans BOOK: ASP.NET Website Programming Problem-Design-Solution 0 March 8th, 2004 01:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.