Solved

Directory Functions Access VBA

Posted on 2011-09-21
5
349 Views
Last Modified: 2012-05-12
I am trying to find a functions in Micorosoft access 2007(vba) that imports a directory structure  into a microsoft access table.   I can get the filename to be inserted into the table.  But I would like to have the Date modified field also added to the table.
GetFiles.txt
0
Comment
Question by:centralmike
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36574398
I'm puzzled - you have attached a text file that does what you are asking - it works for me if iI run it in Access in a database that has a table tblDirectory, with columns FileName and FileDate - have you got the columns defined in the table?
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36574425
The way it is coded, you need to include the filal backslash at the end of the path, or it returns nothing, so something like
Call GetFiles("c:\documents and settings\user\desktop")

Open in new window

0
 

Accepted Solution

by:
centralmike earned 0 total points
ID: 36574548
Hi Andrew, In windows explore we have the four columns.
FileName Size Type Date Modified.  The program imports the FileName column but I would like to import the Date Modified column also through VBA code.  The date your seeing in the text file is just the current date.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36574661
No - it uses the FileDateTime function which according to the documentation contains the date when the file was created or last modified.  I'll paste the code here slightly tidied up:

Sub GetFiles(strPath As String)

    Dim rs As Recordset
    Dim strFile As String
    Dim dtDate As Date
    
    'clear out existing data
    CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError
    
    'open a recordset
    Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)
    
    'get the first filename
    strFile = Dir(strPath, vbNormal)
    'Loop through the balance of files
    Do While strFile <> ""
        
        dtDate = FileDateTime(strPath & strFile)
        rs.AddNew
        'to save the full path using strPath & strFile
        'save only the filename
        rs!FileName = strFile
        rs!FileDate = dtDate
        rs.Update
        
        'try for next filename
        strFile = Dir()
        
    Loop
    
    Set rs = Nothing
    MsgBox ("Directory list is complete.")

End Sub

Open in new window

0
 

Author Closing Comment

by:centralmike
ID: 36908494
The process worked great. Just needed to add a little more code to my function
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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

911 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

20 Experts available now in Live!

Get 1:1 Help Now