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

[Tina Norris Fields]

Thank you, Rocky. I've saved it for a future use.

T

Tina Norris Fields
tinanfields-at-torchlake-dot-com
231-322-2787

On 07/24/17 9:51 AM, Rocky Smolin wrote:
> 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
>>>
>>
>>
>
>
>