You are here:

Using MS Access/File names in a folder to a table in access


Is it possible for me to get the file names in a folder to a table in access 2000.
I have a folder  named MyScan. With the click of a button in a form is it possible to get all the file names in the folder to be in the table named Filename with the field DocName.
Could you kindly help me with a code.

Actually I have code that does just that. What I do is run a batch file (in my case a series of batch files) that makes a text file with the DOS dir listing for the folder and subfolders (in my case the whole drive. This is what is in batch file:
DIR S:\*.accd? /s > S:\accdbfiles.txt

I then have code that opens up the file reads it in and write the info to a table. Following is the code that I use. Sorry I can't trim this specifically for your needs but it should give you enough to go on.

Private Sub cmdUpdate_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strTextLine As String, strIndicator As String, strFolder As String, strSearchFolder As String
Dim dteModDate As Date, dteModTime As Date
Dim lngSize As Long, lngFileID As Long
Dim strFilename As String, strInputFile As String, strBatch As String
Dim sglPause As Single, sglStart As Single

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblMDBFiles", dbOpenDynaset)

'Shell "DIR S:\*.md? > S:\mdbfiles.txt"
'Shell "Dir S:\*.accd? > S:\accdbfiles.txt"
'Shell "DIR X:\*.md? > X:\mdbfiles.txt"
'Shell "Dir X:\*.accd? > X:\accdbfiles.txt"
'Shell "DIR Y:\*.md? > Y:\mdbfiles.txt"
'Shell "Dir Y:\*.accd? > Y:\accdbfiles.txt"

For i = 1 To 6
Me.txtComplete.Visible = True
Select Case i
   Case 1
       strInputFile = "S:\mdbfiles.txt"
       strBatch = "S:\mdbfiles.bat"
       sglPause = 150
   Case 2
       strInputFile = "S:\accdbfiles.txt"
       strBatch = "S:\accdbfiles.bat"
       sglPause = 150
   Case 3
       strInputFile = "X:\mdbfiles.txt"
       strBatch = "X:\mdbfiles.bat"
       sglPause = 100
   Case 4
       strInputFile = "X:\accdbfiles.txt"
       strBatch = "X:\accdbfiles.bat"
        sglPause = 100
  Case 5
       strInputFile = "Y:\mdbfiles.txt"
       strBatch = "Y:\mdbfiles.bat"
       sglPause = 100
   Case 6
       strInputFile = "Y:\accdbfiles.txt"
       strBatch = "Y:\accdbfiles.bat"
       sglPause = 100
End Select
Shell strBatch

sglStart = Timer
Do While Timer < sglStart + sglPause
'    Me.txtComplete = Timer / (sglPause - sglStart)
'    Me.Repaint

Open strInputFile For Input As #1
Do While Not EOF(1)
   Line Input #1, strTextLine
   strIndicator = Left(strTextLine, 10)
   If strIndicator = " Directory" Then
       strFolder = Right(strTextLine, Len(strTextLine) - 14)
       If Right(strFolder, 2) = ":\" Then strFolder = Left(strFolder, 2)
       If InStr(1, strFolder, "'") > 0 Then
         strSearchFolder = Replace(strFolder, "'", "''")
         strSearchFolder = strFolder
       End If
   ElseIf IsDate(strIndicator) Then
       dteModDate = DateValue(Left(strTextLine, 10))
       dteModTime = TimeValue(Mid(strTextLine, 13, 8))
       If Trim(Mid(strTextLine, 21, 18)) <> "" Then
         lngSize = CLng(Trim(Mid(strTextLine, 21, 18)))
         lngSize = 0
       End If
       strFilename = Right(strTextLine, Len(strTextLine) - 39)
       If Right(strFilename, 1) = "b" Or Right(strFilename, 1) = "e" Then
         lngFileID = Nz(DLookup("[FileID]", "tblMDBFiles", "[Folder] = '" & strSearchFolder & "' AND [Filename] = '" & strFilename & "'"), 0)
         If lngFileID = 0 Then
         With rs
         .Fields("ModDate") = dteModDate
         .Fields("ModTime") = dteModTime
         .Fields("Size") = lngSize
         .Fields("Filename") = strFilename
         .Fields("Folder") = strFolder
         End With
         With rs
         .FindFirst "FileID = " & lngFileID
         .Fields("ModDate") = dteModDate
         .Fields("ModTime") = dteModTime
         .Fields("Size") = lngSize
         .Fields("Filename") = strFilename
         .Fields("Folder") = strFolder
         End With
         End If
       End If
   End If
Close #1
Next i

Set rs = Nothing
Set db = Nothing

Me.txtComplete.Visible = False

MsgBox "Update Complete!"
End Sub

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.