Filename import to table

  • Thread starter Thread starter Gym Lyle
  • Start date Start date
G

Gym Lyle

I have a table with 3 fields. The primary key is the name
of a file (xxx.jpg). I have been hand jamming the names
of the newly uploaded files everytime. The filenames are
linked to a directory of jpg's which are viewable in the
database. Is there an easy way to update this table
without typing in each file name?

Gym Lyle
 
Gym,

You can do this with some VB code, by opening your table as a recordset,
scanning your directory and appending new .jpg's. Here's some sample code:

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:\documents\access\")
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

End Sub

In my exampe new .jpg's in folder C:\documents\access\ are appended to table
tblFiles. Change names accordingly.
Filename being your primary key will wnsure you don't get dublicates
(rejected by error handling).

HTH,
Nikos
 
Back
Top