Date Data Last Updated (1 Viewer)

Zippyfrog

Registered User.
Local time
Today, 03:56
Joined
Jun 24, 2003
Messages
103
Thanks for the help on my other question for my report! It is working beautifully because of all the help everyone provided me with.

One more question - how do I make a text box on a report have the date/time a file was last updated?

My situation is I have a file called grades.csv that is exported from our main database (file structure is saved so every column always contains the same data) and I have a linked table that goes to that CSV file. As users export from our main system, they save on top of the grades.csv file, thus the data reporting on is updated. What I want to add to my access report is a textbox that provides the date that grades.csv was last updated so as a user runs the report, they know they are using the most up to date version of data. And if they see an old date, it reminds them that they forgot to export the data before running the report.

I can't seem to figure out how to do this. I was hoping to just be able to refer to a file "Last Changed" date from the file property, but that doesn't seem to be an option in the properties.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:56
Joined
May 7, 2009
Messages
19,170
add a tablr that will record the date of your last import/export.
it may have but 1 record.
after import, use update query to update this table.

use dlookup on your report to get the last date from the table.


or you can query the msysobjects table for Dateupdated for the linked excel table.
 

isladogs

MVP / VIP
Local time
Today, 08:56
Joined
Jan 14, 2017
Messages
18,186
add a tablr that will record the date of your last import/export.
it may have but 1 record.
after import, use update query to update this table.

use dlookup on your report to get the last date from the table.


or you can query the msysobjects table for Dateupdated for the linked excel table.

@Arnelgp
I may be wrong but I don't think either of those methods will work reliably

1. If the CSV file is updated externally, the date in the Access table will not be accurate.
2. The DateUpdate field in MSysObjects gives the date/time the table design was last modified. e.g. a column width was altered or it was sorted in a different order etc etc. Changing the data externally will alter that value but my point is that other changes will also do so.

@zippyfrog
I think the only utterly reliable method of doing this is by grabbing the date modified value from the extended file properties for the csv file concerned using GetDetailsOf.
See https://www.access-programmers.co.uk/forums/showthread.php?t=294548

Any thoughts?
 

MarkK

bit cruncher
Local time
Today, 01:56
Joined
Mar 17, 2004
Messages
8,178
Hey Zippy,
A Scripting.File object exposes a DateLastModified property, which I believe is the Windows LastModified date. To read that property from a file you can do something like...

Code:
Function GetLastModified(Filespec As String) As Date
    Dim f as Object

    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(Filespec) Then
            Set f = .GetFile(Filespec)
            GetLastModified = f.DateLastModified
        Else
            Err.Raise 53   [COLOR="Green"]' file not found[/COLOR]
        End If
    End With

End Function
hth
Mark
 

isladogs

MVP / VIP
Local time
Today, 08:56
Joined
Jan 14, 2017
Messages
18,186
Ah yes. Forgot about FSO. That should indeed work.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:56
Joined
May 7, 2009
Messages
19,170
or simply:

VBA..FileDateTime("FullPath of file")
 

Zippyfrog

Registered User.
Local time
Today, 03:56
Joined
Jun 24, 2003
Messages
103
or simply:

VBA..FileDateTime("FullPath of file")

This looks like the simplest solution. How would I make this appear on a report? I currently have a textbox on my form. What event would I add the VBA to?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:56
Joined
May 7, 2009
Messages
19,170
use the report's Load event, then add:

me.unboundtextbox=vba.filedatetime("fullPathAndExcellFilenamePlusExtension")
 

isladogs

MVP / VIP
Local time
Today, 08:56
Joined
Jan 14, 2017
Messages
18,186
Just to say that you don't need the VBA. part of this code. This works just as well:

Me.unboundtextbox=FileDateTime("FullFileNamePath")
 

Zippyfrog

Registered User.
Local time
Today, 03:56
Joined
Jun 24, 2003
Messages
103
You guys are incredible! Thanks so much - this is exactly what I needed!
 

Users who are viewing this thread

Top Bottom