Solved

Date modified info for all files in a directory-MS Access VBA

Posted on 2012-04-06
7
322 Views
Last Modified: 2012-04-11
This is kind of an add on question related to a question that I posted a while back (See Link).
 
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27239606.html 

I was faced with a situation where I needed to copy the Date Modified information from files and folders located in a specified directory, to a database table.  boag2000 provided a great example that helpd solve the issue I was dealing with.  Now, I would like to know if the code can be expanded upon in order to copy the Date Modified information from all files located in a specific directory, including subfolders, to a database table.

Any input is appreciated.
0
Comment
Question by:giswest
  • 3
  • 3
7 Comments
 
LVL 39

Expert Comment

by:als315
ID: 37818387
You can create table with file name  and Date Modified fields (let table will be tblFiles, fields: FName and FMod), open it and add records in loop:
Dim rst as RecordSet
Set rst = CurrentDB.OpenRecordset ("tblFiles")
.....
For Each fil In fldr.Files
        Debug.Print fil.Path, fil.DateCreated
        rst.AddNew
        rst!Fname = fil.Path
        rst!Fmod = fil.DateCreated
        rst.Update
 Next
........
rst.close

Open in new window

0
 

Author Comment

by:giswest
ID: 37823642
als315,

In your example, don't I need to define the directory where the folders reside?  I defined the fldr variable (SEE EXAMPLE BELOW), but I recieve a Compile Error: Invalid Qualifier.  Based on the error, I don't that it likes the way I defined the variable.

Example:

Dim fldr as String

fldr = "C:\SomeFolder\"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37824229
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 39

Expert Comment

by:als315
ID: 37826414
Check this combination of two solutions
Access--EEQ27665707ListFiles.mdb
0
 

Author Comment

by:giswest
ID: 37833443
als315,

After looking at your sample database, I modified my code to the following:

Dim rst As Recordset
Dim attachmentPathRemote As String
Dim sourceFolder As Folder
Dim sourceSubFolder As Folder
Dim fileItem As File
Dim fso As Object

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Attachment_File_List_Remote"
DoCmd.SetWarnings True

attachmentPathRemote = Form_AttachmentsBeta.DatabaseLocationLoad.Value & "Attachments\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set rst = CurrentDb.OpenRecordset("Attachment_File_List_Remote")

    Set sourceFolder = fso.GetFolder(attachmentPathRemote)
    On Error Resume Next
   
    For Each sourceSubFolder In sourceFolder.SubFolders
        For Each fileItem In sourceSubFolder.Files
       
            rst.AddNew
            rst!File_Name = fileItem.Name
            'rst!Folder_Name = fileItem.ParentFolder.Name
            rst.Update
         
        Next
    Next
   
    Set fileItem = Nothing
    Set sourceFolder = Nothing
    Set sourceSubFolder = Nothing
    Set fso = Nothing
rst.Close
Set rst = Nothing

This produces the result I'm looking for, which is to copy the name and date modified of all files contained the directory subfolders into a specified table.

However, I would also like to copy the name of the Parent Folder (Subfolder) that contains the files.  I've tried adding the following line:

rst!Folder_Name = fileItem.ParentFolder.Name

However, this only returns a single record for each subfolder that contains a file.

For example, my current directory contains 8 folders, with only 5 of the 8 folders containing files.  Without adding the "rst!Folder_Name = fileItem.ParentFolder.Name", the names of all files (13 total) contained in the directory folders are copied to the designated table.  With the "rst!Folder_Name = fileItem.ParentFolder.Name", only the names of the folders containing a file (5 folders) and the name of the first file listed in the folders are copied to the designated table.

Any ideas/work arounds regarding this issue?
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 37833898
0
 

Author Closing Comment

by:giswest
ID: 37834716
als315,

Your sample worked perfectly when I ran it using your table (tblFileList) as the destination table.  When I defined my table (Attachment_File_List_Remote) as the destination table, I ran into the same issue that I previously described, where only the name and date modified information of the folders containing a file were copied.  I copied your table to my database, renamed it (Attachment_File_List_Remote), and re-ran the code.  Everything worked fine.  I re-ran the code I posted earlier, and it also worked fine.  There must have been an issue with my original table, or I must have had some sort of filter on it, which caused the odd behaviour.  Anyhow, I really appreciate the time and you put into solving this issue.

Have a good day.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now