Copy a file if it's newer on the source system... MSAccess

[gbaughma]

OK, so not sure if this was really an Access question, or a filesystem question.

Seems that Access does some pretty funky stuff with its date/time stamps, making it difficult to know when the file was *really* modified.

For example.... create a blank access database.
(Get-Item c:\mydatabase.accdb).LastWriteTime will show you when it was created.

Close it... open it again... don't touch anything, just close it again....
Guess what? LastWriteTime gets updated.

So, herein lies the issue. We have a number of access databases that our developers work on. We have a little powershell script that gets run every login (or on demand) called "IT Fixit" that does a number of things, including copying down those accde files to the local machine (Access is back-ended with SQL in our case, so just the accde gets copied, all the data goes to SQL).

Well, this process normally takes a minute or two, because of the size of the access files.

So I said to myself, "Self, what if we only copied the files that had CHANGED since the last time they were copied?" Sounds simple enough, if LastWriteTime and LastAccessTime didn't CHANGE EVERY TIME you even touched the Access database.

Even the HASH changes, simply by opening and closing a blank access database!

This is making it quite difficult to know if the source file has actually changed, and to have something to compare with to know if I could re-copy the file or not.

Here's what I do know:
The CreationTime attribute is copied from the source location, whether the file is created NEW or [stopping post]


OK... in the time I was typing this, we brain-stormed a work-around... and I'm going to share it here for posterity.

So, here is what we figured out.

1) We needed a way to know, and remember, when a source file was modified, and if that modification was later than the file on the local machine.
2) Since the LastWriteTime and LastAccessTime BOTH updated any time the local Access database was opened, that wasn't a good indicator... for example, I couldn't compare the LastWriteTime on the local machine to the LastWriteTime on the server.

SO... here's what we ended up doing.
Compare the local CreatedTime to the source LastWriteTime
If they are different, copy the file
(Now, here's the cool part)
UPDATE the local CreatedTime to match the source's LastWriteTime

... now, I'm using the CreatedTime on the local machine essentially as a variable to compare against the source LastWriteTime. Now, if the source gets touched, it gets copied down to the local machine.

Here's the function I wrote to do it.

function Copy-ItemIfDifferent($SourceFile, $DestinationFile) {
if ((Get-Item $SourceFile).LastWriteTime -ne (Get-Item $DestinationFile).CreationTime)
    {
    # The file has changed since it was last copied to the local folder...
    AddText("Updating " + (Split-Path $SourceFile -leaf))
    Copy-Item $SourceFile $DestinationFile -Force | Out-Null 
    # Push the date from the source to the destination, using "CreationTime" as a high water mark for the next comparison
    (Get-Item $DestinationFile).CreationTime = (Get-Item $SourceFile).LastWriteTime
    }
 }

Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg