Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2008
    Posts
    6

    Thumbs up Unanswered: Access: Form Analyser

    I am trying to clean up a database, which has many Macros Querries Modules.
    I am trying to create some code (VB), which will read every form in the application then look at each control in the form.

    For Each Form in application
    Extract Event Code
    Extract Event Expression
    Extract Event Macro
    For Each Control in Form
    Extract Code
    Extract Expression
    Extract Macro
    Next Control
    Next Form

    When I have this data I can determine what is actually being used.
    I just want the basic frame work.

    Thanks for helping

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And you want us to do just write the code for you?

    Little lazy aren't ya?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2008
    Posts
    6

    Reply to ridicule

    No I don't want the code to be written.
    I would like directions to a place where the "For Each" is documented and/or I would like directions to existing software.
    The database I am trying to analyse has been around @ 5 years and it has been polluted.
    I am retired and do not know much about Access, I used to work with mainframes.
    I am doing this work pro bono as a volunteer.
    The organization is a nature conservancy.

    Hoping this explains situation
    Frank Wallace

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well, the For Each command would be documented in the Microsoft Access Help system.

    I don't know of any existing software that would do this.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    This is your Access bible:

    http://msdn2.microsoft.com/en-us/library/bb243746.aspx

    In particular, you want to look at the Access Object Model and in there, the object map. The CurrentProject object is probably where you'll start.

    Those collections within CurrentProject make it pretty easy to step through all the forms and reports and such and enumerate all the objects within them.

  6. #6
    Join Date
    Jan 2008
    Posts
    6

    A little more

    I will try your suggestion Sco08y.
    This is what I have now, but it requires opening each form
    Sub subComponentsForms()
    On Error GoTo subComponentsForms_Error
    'Write All Form Names and All Form Controls
    '_________________________________________________ _________________________________
    Dim objFormControl As Control
    Dim objForm As Object
    Dim strFormName As String
    Dim strFormControlName As String
    '_________________________________________________ _________________________________
    For Each objForm In CurrentProject.AllForms
    strFormName = objForm.Name
    Write #1, "FORM: ", objForm.Name
    '************************************************* ********************
    'Form has to be open to get at Controls, is there another way todo it?
    '************************************************* ********************
    DoCmd.OpenForm objForm.Name, , , , , acHidden
    For Each objControl In Forms(objForm.Name).Controls
    strFormControlName = objControl.Name
    Write #1, "CONTROL NAME: ", objControl.Name
    Next objControl
    subComponentsForms_NextForm:
    Next objForm
    subComponentsForms_Exit:
    Exit Sub
    subComponentsForms_Error:
    If Err.Number = 2580 Then
    Write #1, objForm.Name, "Record Source does not Exist"
    Resume subComponentsForms_NextForm
    End If
    MsgBox "subComponents Error: " & Err.Number & " " & Err.Description
    Resume subComponentsForms_Exit
    End Sub

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think you have to open each form in order to access it's controls and properties, but you can open it in Design rather than normally.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    possibly a naïve suggestion, but doesn't the in-built documenter do +/- what you want:
    menu Tools/Analyze/Documenter

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I don't know anything about this site and its products, other than I have seen it advertised in a magazine I used to get. Their analyzer product may help you, but if you are volunteering for a not for profit organization then you budget is probably limited. Here is the site:

    http://www.fmsinc.com

    I don't have any experience writing code to document a database/application, but I do have quite a bit of Access experience. And I think what you are doing is admirable. So if you want or need help, feel free to PM me with questions or any small tasks that I can do to help.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Early conratulations to DC for you first K!

    Izy has the right idea; there is a reasonable self-documentor build into access; why bother re-inventing something thats already there?!
    Last edited by gvee; 02-12-08 at 07:47. Reason: "Early" doesn't have a "W" in it
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2008
    Posts
    6

    Thanks to everybody

    You all have been tremendous.
    I will try to read up at Microsoft.
    I will look at the software.
    I will look at the Documentor.
    The fact is the organization might go to some other database, I don't know much about Access, but it's the only PC database I have any knowledge of, if they get something else I will be completely redundant.
    There allways seems to be several ways of doing something in Access, some of it recent some of it ancient, I tend to end up with a mess.
    I am opening the forms in design view, I am also closing them after I have extracted data.

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good luck to you

    Recommend Access!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Jan 2008
    Posts
    6

    Solution of sorts

    I ran the tools analyser for all forms in the database and extracted the VB code whick I exported to a TXT file, which I was able to read into my program, this enabled the analysis of form interactivity thus allowing the identification of redundant forms. I would of liked to have extract the VB code for all forms within my form but I could not find a method.
    I again thank everybody who helped me.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    so you really do want to reinvent the wheel!
    google "Application.SaveAsText"

    you can get the general idea in the immediate window:
    application.SaveAsText acform, "myForm", "c:\temp\myFormAsText.txt"

    works with all the usual objects

    output is not the same as documenter, but is interesting.

    also interesting is the related function .LoadFromText

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Jan 2008
    Posts
    6

    Thanks izyrider et al

    I have been able to analyse the database and identified several problems.
    I thank you and everybody else who helped.

    Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •