Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    11

    Unanswered: Loading all file names from a static directory to an MS Access Table

    I am looking for a way to have Access list all of the file names into a table from a single directory.

    I would be looking to add all files from directory c:/fileloads/*.*
    to tblfilenames.

    I found the following code but have been unsuccessful...

    Sub getFileNames()
    Dim strFileName As String
    FileName = dir("c:/fileloads/*.*")
    Do While FileName <> ""
    Debug.Print strFileName
    table.tblfilenames!name
    strFileName = dir()
    Loop
    End Sub
    Any help would be greatly appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Please could you define "unsuccessful"? What happened? What didn't? Errors? etc
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2009
    Posts
    11
    Thanks for getting back! I am getting

    Run-time error '424':
    Object required

    on table.tblfilenames!Name = strFileName

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That line is not in your posted code
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2009
    Posts
    11
    sorry, "table.tblfilenames!name" was the line... I was troubleshooting when I posted...

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A few errors in there:
    Code:
    Sub getFileNames()
    
        Dim FileName As String
        FileName = Dir("C:\fileloads\*.*")
        
        Do While FileName <> ""
            Debug.Print FileName
            FileName = Dir()
        Loop
        
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2009
    Posts
    11
    Thanks! Here is a code set that I have found that is working the way I was wanting it to:

    Code:
    Create a table named tblDirectory with 2 fields: 
    FileName  (Text 250) 
    FileDate    (Date/Time) 
    
    
    Call the code below by pressing Ctrl-G to open the debug window and type: 
    GetFiles("c:\windows\") 
    
    
    Paste this code into a regular module: 
    
    
    Sub GetFiles(strPath As String) 
       Dim rs As Recordset 
       Dim strFile As String, strDate 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 
          'check to see if you have a filename 
          If strFile = "" Then 
            GoTo ExitHere 
          End If 
          strDate = FileDateTime(strPath & strFile) 
          rs.AddNew 
          'to save the full path using strPath & strFile 
          'save only the filename 
          rs!FileName = strFile 
          rs!FileDate = strDate 
          rs.Update 
    
    
          'try for next filename 
          strFile = Dir() 
       Loop 
    
    
    ExitHere: 
       Set rs = Nothing 
       MsgBox ("Directory list is complete.") 
    End Sub
    Can someone help me run this code from a macro? Whenever I go to run it directly from the code, it pops up with a prompt to choose what macro to run... This is running in 2007. Everytime I think I am getting the hang of it, I find out that I dont. LOL

    Old job was on access 97 *sigh*...
    Last edited by NotaDBGuru; 08-27-09 at 20:31.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •