Extract Access Query Report Name From Multiple Access Databases

Topic

New Reply

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?

Reply | Quote

Replies

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote