[AccessD] [dba-OT] Access files by date

[Rocky Smolin]

Perfecto, mi amigo.

Thank you.

Here's my finished function to load the file table and rename the files on
chronological order in case it's of value to someone else. It could be
generalized by removing the referenced to "Photos", and strFileSpec to "*.*"
In the name statement you'd have to get the file extension of the original
file instead of just adding ".jpg". And passing strFolder and strFilespec to
the function as arguments would have been a nice touch.  But for the moment
this is a one-off for me.


Best,

Rocky

Option Compare Database
Option Explicit

Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

Private Sub Command0_Click()

strFolder = "C:\temp\"
strFileSpec = "*.*"
 
MsgBox GetPhotos & " And Done."

End Sub

Function GetPhotos() As Long

  Dim strFileName As String
  Dim strNewName As String
  
  Dim rs As DAO.Recordset
  
  Dim lngCount As Long
  Dim lngFileNumber As Long
  
  CurrentDb.Execute "Delete * FROM tblPhotos"
  
' Get files into table
  Set rs = CurrentDb.OpenRecordset("Select * FROM tblPhotos")
  MsgBox strFolder & strFileSpec
  strFileName = Dir$(strFolder & strFileSpec)
  While strFileName <> ""
       rs.AddNew
       rs!fldPhotosFileName = strFileName
       rs!fldPhotosFileDate = FileDateTime(strFolder & strFileName)
       rs.Update
       lngCount = lngCount + 1
      strFileName = Dir$
  Wend
  rs.Close
  Set rs = Nothing
  GetPhotos = lngCount
  
' Rename files
    lngFileNumber = 0
    
  Set rs = CurrentDb.OpenRecordset("Select * FROM tblPhotos ORDER BY
fldPhotosFileDate")
  Do While rs.EOF = False
    lngFileNumber = lngFileNumber + 10
    strNewName = strFolder & "RR" & Right("0000" & Trim(Str(lngFileNumber)),
4) & ".jpg"
    Name strFolder & rs!fldPhotosFileName As strNewName
    rs.MoveNext
Loop

  End Function


-----Original Message-----
From: dba-OT [mailto:dba-ot-bounces at databaseadvisors.com] On Behalf Of
Stuart McLachlan
Sent: Monday, July 24, 2017 1:20 AM
To: 'Off Topic'; Access Developers discussion and problem solving
Subject: Re: [dba-OT] [AccessD] Access files by date

That was air code.  Quite a few typos and simpel erros there :(

This one should run:

Function GetPhotos() As Long
 Dim strFileName As String
 Dim rs As DAO.Recordset
 Dim lngCount As Long
 Set rs = CurrentDb.OpenRecordset("tblPhotos")
 strFilename = Dir$(CurrentProject.path & "\*.jpg")  While strFilename > ""
      rs.AddNew
      rs!FileName = strFilename
      rs!Filedate = FileDateTime(CurrentProject.Path & "\" & strFilename) 
      rs.Update lngCount = lngCount + 1
     strFilename = Dir$
 Wend
 rs.Close
 Set re = Nothing
 GetPhotos = lngCount
 End Function



On 24 Jul 2017 at 18:09, Stuart McLachlan wrote:

> I'd create a table to store all the filenames and dates with whatever 
> other fields you need for new filenames, comments or whatever.  Then a 
> simple function:
> 
> Function GetPhotos() As Long
> Dim strFName As String
> Dim fdate As Date
> Dim rs As DAO.Recordset
> Dim lngCount As Long
> Set rs = CurrentDb.OpenRecordset("tblPhotos")
> strFilename = Dir$(CurrentProject.payh & "\*.jpg") While strFilename > 
> ""
>      rs.AddNew
>      rs!FileName = strFilename
>      rs!Filedate = fdate = FileDateTime(CurrentProject.Path & "\" &
>      strFilename) rs.Update lngCount = lngCount + 1
>     strFilename = Dir$
> Wend
> rs.Close
> Set re = Nothing
> GetPhotos = lngCount
> End Function
> 
> Once you have then in the table you can do anything you want with the 
> data, manually or with VBA.
> 
> On 23 Jul 2017 at 21:36, Rocky Smolin wrote:
> 
> > Dear List:
> > 
> > 
> > 
> > Having just returned from a 5 day 250 mile bike trek through the 
> > redwoods of northern California with a group of 0 guys, I have 
> > volunteered to assemble everyone's pictures and edit them down to a 
> > director's cut.
> > 
> > 
> > 
> > What I have done after editing all their photos (and discarding
> > many) is put everyone's pictures in one folder which I can then 
> > display by date.  Since everyone (hopefully) has a correct date time 
> > stamp, the pictures of like places and times are now grouped 
> > together.
> > 
> > 
> > 
> > So now I would like to loop through these picture in date sequence, 
> > and rename them RR00010, RR00020, RR0030, etc.  so I can then do a 
> > bit of rearranging by changing file names. Once that's done I can do 
> > a final cull of the pictures and, if I need to manually insert any 
> > in a different place I can do it - like RR00025 would go between
> > RR00020 and RR00030.
> > 
> > 
> > 
> > I'm having a little though, trouble extracting this solution from 
> > the web. Can someone point me in the right direction?  Can this all 
> > be done with FSO? My first attempt retrieved the pics in file name 
> > order, not date/time order. And don't see a way using the Dir 
> > command to do that.
> > 
> > 
> > 
> > MTIA
> > 
> > 
> > 
> > 
> > 
> > Rocky Smolin
> > 
> > Beach Access Software
> > 
> > 760-683-5777
> > 
> >  <http://www.bchacc.com> www.bchacc.com
> > 
> >  <http://www.e-z-mrp.com> www.e-z-mrp.com
> > 
> > Skype: rocky.smolin
> > 
> > 
> > 
> > 
> > 
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> 
> 
>