?
Solved

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

Posted on 2012-04-06
7
Medium Priority
?
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 40

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 40

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 40

Accepted Solution

by:
als315 earned 2000 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

764 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