Results 1 to 3 of 3
  1. #1
    Rendon115 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2019
    Posts
    10

    Large Import of Files into Table

    Ive tried several different blocks of code, including Allen Browne's. I have around 15,000 zip files in a folder on our work server. What I am trying to do, is (ideally) once a day update the table I have in Access with the Zip file names, location, and date modified. Ive tried a few different methods, but none of them seem to be very efficient. I was wondering if anyone could steer me in the right direction with this. Would using something outside access work in order to speed up the process maybe? Any help would be appreciated, thank you

    This is what I currently have, after about 6-7 mins I got about 4000 listed
    Code:
    Sub ListFiles(mySourcePath)
          
        Dim Counter As Integer
          
        Set myObject = New Scripting.FileSystemObject
        Set mySource = myObject.GetFolder(mySourcePath)
        
          
        On Error Resume Next
          
        Dim db As Database
        Dim rs As Recordset
        Dim mysql As String
          
        Set db = CurrentDb
        mysql = "Files"
        Set rs = db.OpenRecordset(mysql, dbOpenDynaset)
          
        For Each myFile In mySource.Files
            rs.AddNew
            rs![FPath] = myFile.path
            rs![FName] = myFile.Name
            rs![FDate] = myFile.DateLastModified
            rs.Update
              
            Counter = Counter + 1
        Next
          
        ' Clean up
        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing
         
        Set myObject = Nothing
        Set mySource = Nothing
         
        MsgBox "Listed " & Counter & " Files."
          
    End Sub


  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you know the drives involved, you can probably adapt this to suit your needs. It runs from the command prompt and is very quick.

    I use this approach to find and list various file types and their locations to a text file, then read the text file to an Access table..

    Code:
    C:\Users\Jack>dir /b /s *.zip >c:\users\jack\documents\oldzips.txt
    I just used this to get a listing of all zip file on my C: drive and put the list into oldzips.txt

    Here is a partial listing of the output.
    Code:
    C:\Use:\Users\Jack\ColinTool.zip
    C:\Users\Jack\AppData\Local\Microsoft\Office\16.0\PowerQuery\User.zip
    C:\Users\Jack\AppData\Local\Temp\AttachDemo.zip
    C:\Users\Jack\AppData\Local\Temp\database11111.zip
    C:\Users\Jack\AppData\Local\Temp\demoDataMacro.zip
    C:\Users\Jack\AppData\Local\Temp\Review Tracking-forum.zip
    C:\Users\Jack\AppData\Local\Temp\SuperHeroes-1.zip
    C:\Users\Jack\AppData\Local\Temp\SuperHeroes-2.zip
    C:\Users\Jack\AppData\Local\Temp\SuperHeroes.zip
    C:\Users\Jack\AppData\Local\Temp\SuperHeroes3.zip
    Good luck.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the last thing you want to do is use code to loop thru data to do updates.
    instead you run an update query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import multiple XML files into a table
    By miloann2002 in forum Import/Export Data
    Replies: 2
    Last Post: 08-01-2016, 01:50 PM
  2. VBA Commands To Import DBF Files Into A New Table
    By wycliffslim in forum Access
    Replies: 1
    Last Post: 02-26-2015, 05:07 PM
  3. Browse to import *.txt or *.xls files into table
    By Dirtpics in forum Import/Export Data
    Replies: 2
    Last Post: 06-18-2014, 10:25 AM
  4. Replies: 1
    Last Post: 06-27-2012, 07:39 AM
  5. Import multiple dbf files into existing table?
    By sbg2 in forum Import/Export Data
    Replies: 0
    Last Post: 06-12-2006, 02:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums