Solved

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

Posted on 2012-04-06
7
319 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
Comment Utility
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
Comment Utility
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
Comment Utility
0
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.

 
LVL 39

Expert Comment

by:als315
Comment Utility
Check this combination of two solutions
Access--EEQ27665707ListFiles.mdb
0
 

Author Comment

by:giswest
Comment Utility
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
Comment Utility
0
 

Author Closing Comment

by:giswest
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

743 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

17 Experts available now in Live!

Get 1:1 Help Now