You are here:

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

Advertisement


Question
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.AddNew
rst!StudFiles = strFileName
rst.Update
strFileName = Dir()
Loop

rst.Close
Set rst = Nothing
dbs.Close
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.

Example:
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.

Answer
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.AddNew
rst!StudID = rst1!StudID
rst!StudFiles = strFileName
rst.Update

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.