You are here:

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

Advertisement


Question
Hi
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.

Answer
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
Loop

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, "'", "''")
       Else
         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)))
       Else
         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
         .AddNew
         .Fields("ModDate") = dteModDate
         .Fields("ModTime") = dteModTime
         .Fields("Size") = lngSize
         .Fields("Filename") = strFilename
         .Fields("Folder") = strFolder
         .Update
         End With
         Else
         With rs
         .FindFirst "FileID = " & lngFileID
         .Edit
         .Fields("ModDate") = dteModDate
         .Fields("ModTime") = dteModTime
         .Fields("Size") = lngSize
         .Fields("Filename") = strFilename
         .Fields("Folder") = strFolder
         .Update
         End With
         End If
       End If
   End If
Loop
Close #1
Next i

Set rs = Nothing
Set db = Nothing

Me.txtComplete.Visible = False

Me.Requery
MsgBox "Update Complete!"
End Sub

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.