Tip of the Day

Web Hosting by Netfirms | Free Domain Names by Netfirms

Tip of the Month: File Inventory Tool

The File Inventory Tool

Motivation

My original plan was just to read some online books (HTML format) on my CD. I had lots of them, you know, and sometimes, it's hard to remember whether I already have the book for a certain topic, say C++, or not. I don't want to buy or look for another book when in the first place, I already have them somewhere. The problem is to locate the book in CDs. I was thinking, how will I make my life easier.

But of course! Why not type-in my file names into a database?... That way, I only have to check my database, and then I can generate a report to get my verification. Sounds simple?

Goal

Create a tool to read in the directory/file names and input those names into a database.

What does it do?

The tool should be able to fill up the database with the names of the directory or files in the directory...representing the type of items I want to inventory. If the desired location contains books, then the database will contain information pertaining to books. If the desired location contains mp3 files, the database will contain titles of the songs.

Limitation

This will only store the folder/file names in the next level from the specified directory. It will not traverse all sub-folders in that location. This is a limitation that is a must since I only wanted to store titles of books into a database... If I wanted to store all files in my hard drive, then, that's another story...If you want that tool immediately, let me know. For now, that has to wait.

Tools used

I have MS Access as a desktop database tool. It includes Visual Basic for Applications which I can leverage to achieve the goal...hopefully.

The Code

Private Sub cmdReadFiles_Click()
   Dim DirName As String    ' Folder or File name
   Dim PathName As String ' desired path containing
                                          ' items to store in database
   On Error GoTo Err_cmdReadFiles_Click

   Screen.PreviousControl.SetFocus

' Extract the drive information
' txtPath is a text control field that prompts for the
'   path to be traversed.
   PathName = Mid(txtPath.Value, 1, _
           InStr(txtPath.Value, ":"))
   ' change to that drive
   FileSystem.ChDrive (PathName)

' Extract and change to the folder containing
             ' the items to be added into the database
   PathName = Mid(txtPath.Value, _
              InStr(txtPath.Value, ":") + 1)
   FileSystem.ChDir (PathName)

' Start traversing the items in the desired directory
   DirName = FileSystem.Dir("", vbDirectory)
   Do While DirName <> "" ' Start the loop.
       ' Ignore the current directory and the
     ' encompassing directory.
       If DirName <> "." And DirName <> ".." Then
       ' Use bitwise comparison to make sure
       ' MyName is a directory.
             If (GetAttr(DirName) And vbDirectory) = _
                 vbDirectory Then

                 ' Add new entry to the database
                 DoCmd.GoToRecord , , acNewRec
                 txtPath.Value = DirName
                     ' Display entry only if it
                 Recordset.AddNew

                 ' Add information and update database
                 Recordset!Title = DirName
                 Recordset.Update
            End If ' it represents a directory.
       End If
      DirName = FileSystem.Dir   ' Get next entry.
   Loop

Exit_cmdReadFiles_Click:
   Exit Sub

Err_cmdReadFiles_Click:
   MsgBox Err.Description
   Resume Exit_cmdReadFiles_Click

End Sub

This is a very crude implementation yet. I do hope that the tip will be useful to you now or someday, in one way or another. I will re-write this documentation as I test this tool some more. Meantime, it's already midnight and I still have to catch some sleep.

Heart of the Solution

The solution is presented above. That's the Visual Basic code that performs the actual traversal of all the folders in a certain location (or path).

It also includes the feature that will store each of the folder/file names found in the specified location into the Access database.

This implementation is custom-implemented for the goal I want to achieve. You must modify the functionality in the code itself if you want to add other information.

Also, this is part of a database that should have been pre-designed. The field "Title" is the field to hold the titles of my books. If you have another type of information to keep, you must have your own descriptive fields in your database.

Application and future enhancements

This is a very basic documentation yet. I will re-write this tip in the next few days. This tool has just been implemented about an hour ago... so this is very fresh from the factory.

I will detail to you the steps from start to finish on how I implemented this small program.

You can use this tip to help give yourself an easier life tracking those many files in your hard drive.

Chapter...more again tomorrow.