Solved

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

Posted on 2012-04-06
7
324 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

775 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