You are here:

Using MS Access/Get File Names to Table (Access 2000)


I have a folder named Scanned Documents with many sub folders with files in it.
I got the following code from the net to get the file names from a particular folder to a table ‘tblStudentFiles’ that works fine.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strFolderPath As String

strFolderPath = "\\SchoolOffice\e\Scanned Documents\" & Me.[Student Id] & "\"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblStudentFiles", dbOpenDynaset, dbAppendOnly)

strFileName = Dir(strFolderPath & "*.*")
Do While strFileName <> ""
strFileName = Left(strFileName, InStrRev(strFileName, ".") + 3)
rst!StudFiles = strFileName
strFileName = Dir()

Set rst = Nothing
Set dbs = Nothing

Could you please help me modify this code so that I can get the file names from several folders to ‘tblStudentFiles’ with folder name (Student Id) in one filed and file name in another field.

‘tblSelectedfiles’ contains the names of the selected folders from which I have to get file names.

StudId   StudFiles
4013     Application Form.pdf
4013     Conduct Certificate.Pdf
4013     Marklist.pdf
2301     Application Form.pdf
2301     Marklist.pdf

StudId is the name of the folder
StudFles is the files in the folder.

The way the code currently works, you are triggering it from a form that is displaying a current student. So it will only work on the current student. If you want to run this code for ALL students or a specified list of students, you will need to add an outer loop to the code.

You need to define a second recordset that lists the StudentIDs you want to catalog files for.

You then loop through that recordset first, then run the current loop within it. So instead of taking the folder name from the form you replace that line (within the outer loop) with something like:

strFolderPath = "\\SchoolOffice\e\Scanned Documents\" & rst1!StudID & "\"

Then add a line in the inner loop before you populate StudFiles:

rst!StudID = rst1!StudID
rst!StudFiles = strFileName

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

©2016 All rights reserved.