Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Extract Access Query Report Name From Multiple Access Databases

    Have a user that was transfered and left a huge directory of reports. Spread out within multiple directories are many Access databases with no index of any kind. Now I am tasked to find the correct one when a department calls and needs a report run.

    Is there a way to extract the Access report name, Access database name, and directory and create a report so I won't have to continue searching for hours to locate a single report?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dseed,

    Yes it is possible using VBA {Visual Basic for Applications} but it is not a trivial task. What I would do is probablly use Excel and write code to loop through the directory tree looking for .mdb files then use automation to create an Access object, open the file then using the Visual Basic for Applications Extensibility 5.1 library capabilities run through the Report Objects class and extract all the report names and then record the Directory,DB name, and report name in successive columns of the Excel worksheet. This would then allow you to easily sort/search the list. I hope this gives you an idea of what is involved and at least one way to approach the task.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    As a longer term objective, you might want to consider importing all of the reports and data sources into a single database, and then create a menu to let you quickly find the correct report. That's not a trivial task either, but your situation sounds like it is a rather large mess at the moment.
    Wendell

  4. #4
    New Lounger
    Join Date
    Jun 2010
    Location
    Gurnee, IL, USA
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I use a product called Speed Ferret. Couldn't reverse engineer (efficiently) without it. However, the publisher stopped updating it at Access 2003, and I haven't tried it with later Access versions. All the same, if you have Access 2003 or older mdbs, this will save you a LOT of time.
    http://www.moshannon.com/speedferret.html
    Regards,
    Kirk

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The Database Documenter will produce a list of reports in a database. You will find it Database Tools. You could run this in each database, send each report to Word then consolidate them all into a single document.
    Regards
    John



Posting Permissions

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