• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

Looping Through Access Database and Build a table of the Report Names

I need to document and build a table with the names of all the Access 2007 Reports within a whole company.  The databases (around 30 or so) are located in a folder and then subfolders per department.  There are some databases...like copies of database or backups of databases that I would like to skip.
Starting with a new blank Access Database, Is there a way, through code to loop through the databases and pull the report names building a new table with the names in the new access database?  I would need to get their names, database name and possibly the location of the database.
Thanks!
0
TanyaDH
Asked:
TanyaDH
  • 4
  • 3
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 Attached is a simple find DB's utility.  You can get all the reports by looping through the documents collection like the following:

Function DoAllReps()

        ' This function loops through all reports

        Dim db As Database
        Dim i
10      Set db = DBEngine.Workspaces(0).Databases(0)
20      For i = 0 To db.Containers("Reports").Documents.Count - 1
30        Debug.Print db.Containers("Reports").Documents(i).Name)
40      Next i

End Function


  Just add the loop to the existing code and you will be good.

Jim.
FindDBs.zip
0
 
TanyaDHAuthor Commented:
Thank you for the quick response.  I took a look at it...had to convert it to Access 2007.  Your file is a .mdb, the databases I will be using are all .accdb.  So we have a conflict there.  I did change in the code the line that refers to look for *.mdb to *.accdb. I copied the function you said to add to the existing code and pasted into the module.  Not sure where you want it added and not sure how to call it within the existing code.  I need step by step help...sorry.
 I ran it and get an error:  Error 52-Bad file name or number. I close the error and it opens another 2 times and then it locks up.  i have to exit using the task manager.
I don't want the user to have to type in the path.  The databases will always be at the same location.  Just want to build a table with all the report names, database name it is from and the location of the database.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I copied the function you said to add to the existing code and pasted into the module.  Not sure where you want it added and not sure how to call it within the existing code.  I need step by step help...sorry.>>

  You didn't need the whole function, just the loop.  What you'll need to do is modify the FindFiles() procedure.  Change this section:

           Case Else
             GoTo UnexpectedOpenError
           End Select
           
         End If
         
         rstFiles.AddNew
         rstFiles![Name] = FileName
         rstFiles![Location] = path
         rstFiles![DateModified] = FileDateTime(path & FileName)
         rstFiles![Version] = strVersion
         rstFiles![ErrorMsg] = strErrorMsg
         rstFiles.Update

to:

           Case Else
             GoTo UnexpectedOpenError
           End Select
           
         End If

         For intI = 0 To db.Containers("Reports").Documents.Count - 1
       
          rstFiles.AddNew
          rstFiles![Name] = FileName
          rstFiles![Location] = path
          rstFiles![DateModified] = FileDateTime(path & FileName)
          rstFiles![Version] = strVersion
          rstFiles![ErrorMsg] = strErrorMsg
          rstFiles![ReportName] = db.Containers("Reports").Documents(intI).Name)

          rstFiles.Update
         Next intI

 Dim intI as integer at the top of the procedure.  Also add the field  RerportName to the table tblMDBs.

<<I ran it and get an error:  Error 52-Bad file name or number. I close the error and it opens another 2 times and then it locks up.  i have to exit using the task manager.>>

  not sure what that's about. Try a simple path, like C:\ and see if it works.

<<I don't want the user to have to type in the path.  The databases will always be at the same location.  Just want to build a table with all the report names, database name it is from and the location of the database. >>

  Just modify the form control to default the path or ditch the form entirely.  If you look at the button code, all it does is call a function anyway.  

Jim.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jeffrey CoachmanCommented:
<No points wanted>

Just some tips.

Just get one part of Jim's solution work fully, then add the other part.

For example, instead of trying to do all of this in one felled swoop, ...then end up trying to troubleshoot multiple issues...
...Simply get the DB looping code to work, to simply spit out a list of all the databases in a folder
Get this working first...

Then get the "Loop Reports" code working (again, independently) for *one* database. (Spit out a list of all reports)

Now with both systems working independently, it should be a simple task to marry the two.

;-)

JeffCoachman
0
 
TanyaDHAuthor Commented:
Excellent!!!  Thank you so much...worked perfectly!
0
 
TanyaDHAuthor Commented:
JDettman, If you are still around...I had a small issue:

There are password protected databases, which I know all of the passwords, how do I get the report listing from these?
Or should I open a new question?

Thanks!
0
 
Jeffrey CoachmanCommented:
0
 
Jeffrey CoachmanCommented:
...but full points to Jim for answering your Q directly...
0
 
TanyaDHAuthor Commented:
Thank you that did the trick!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 Thanks Jeff!

Jim.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now