Finding Last Modified Dates for an Access file and an Access table 1

[IoMatua]

Hi.

I have an Access database that contains certain values which I can extract using VBA in Excel, then I perform calculations on those values and then store the results into an Excel spreadsheet.

What I want to do is to check the .mdb file to see if it was modified since the last time I acessed it from the Excel macro (I'll probably store the Last Accessed date inside an INI file) and if it has been modified, go to the table that contains the values I need, and check to see if that was modified. If it has been modified, redo the calculations and store the new results back into the Excel sheet.

Hopefully the explanation of my problem is sufficient. Any help with this will be greatly appreciated.
Thanks in advance.

 

[SchizoPhreddy]

Hi IoMatua,

I think that the best way to retrieve any file related information / attributes is to use the FileSystemObject (scrunn.dll). This is a standard Microsoft component, so there will be no problems in using it within VBA.

The following function takes in the name of a file and returns the data the file was last accessed.

Function GetLastAccessed(ByVal strFile As String) As Date
Dim myFSO As Object
Dim myFile As Object

' instantiate reference to filesystemobject
Set myFSO = CreateObject("Scripting.FileSystemObject&quot
' assign file to memory
Set myFile = myFSO.GetFile(strFile)

' get DateLastAccessed
GetLastModified = myFile.DateLastAccessed
End Function

DateLastModified can be used in place of DateLastAccessed.

I hope this helps.

Schizo

 

[marduk813]

Schizo, I was trying to use the code you posted here to check the DateLastAccessed on the active document (I'm using Word). The idea is, I want to compare it against a registry value and see which one is earlier. I can get the ActiveDocument.Name and store it in a string variable, but when I try to get any file data from it (DateLastAccessed, etc.) I get a "file not found" error. In fact, it looks like no matter what file name I pass to the function, I get the same "file not found" message.
Am I missing something?

 

[Databaseguy]

Make sure you include the path and file name when passing it tp the proc.

 

[marduk813]

I found a way to avoid using DateLastAccessed altogether, but thank you for the reply Dbaseguy. Your suggestion is probably correct anyhow.