File list to table

[Tom]

using access 2002 we would like to add the file names in a designated folder
and append them to a table.

any advice and sample code to achieve this would be appreciated

thanks in advance for all input

tom

 

[Nikos Yannacopoulos]

Tom,

Here's some sample code that puts all file names in a folder in an
(existing) table called tblFiles, with a single text field (make it
long, 255 length is advised):

Sub filenames_to_table()
Dim fldr, fls, fl
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.GetFolder("C:\MyFolder\")
Set fls = fldr.Files
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblFiles")
On Error Resume Next
For Each fl In fls
If Right(fl.Name, 4) = ".jpg" Then
rst.AddNew
rst.Fields(0) = fl.Name
rst.Update
End If
Next fl
On Error GoTo 0
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Just change C:\MyFolder to the actual folder name, and comment out the
If / End If lines in the For/Next loop if you don't want a filter
applied (this one will only put .jpg file names in the table).

Note: To run this code, it is required to have an appropriate DAO Object
Library reference. While in the VB editor window, go to menu item Tools

References; check if a Microsoft DAO reference is present among the

ones checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos

 

[Tom]

Hi Nikos

Thanks for your help - had to dim fs - but that does exactly what we want &
the jpg filter was a real bonus


Tom