Preserve mdb modified date 1

[sxschech]

I have a manual process where I copy an mdb file from one directory to another and then change the file name. Using this method preserved the last modified date.

I added code to a button on a form within the mdb file to be copied so that it would do the copy and rename for me, however, since access changes the modified date once the file is open, it shows the current date and time rather than the previous date and time. It's probably not a big deal, but I would rather see the modified date as last week rather than today, so that I know that this was really from last week rather than that I needed to open/use the file today. In the renaming process, I append a date, so I can go by that date, but sometimes, I may need to go back to that earlier version and if it shows today's date in the modified, then I wouldn't necessarily know if in fact it was used today or last week.

The other reason I decided to put the procedure in the file is so when someone else has to use the mdb, I don't have to explain to them to copy and rename the file all they need to do is click the button.

Any ideas on preserving the modified date?

 

[MasterRacker]

Instead of building this into the DB itself, how about writing a VBScript to copy and rename the file then launch it?

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[sxschech]

Main reason that I wanted it in the db itself was so everything was in one place (self contained) and I wouldn't have to tell other people "remember, before opening the db, run this other program and then open the db". Besides I would still need to link into the db in order to get the information I need to generate the file name and location so I don't have to hard code the values.

 

[MasterRacker]

Understood, but you might be out of luck. I'm pretty sure the modified date will change as soon as you open the file. You could query the DB in the launcher script and yes, you would have to train users to use the launcher instead of directly opening the DB.

Can you give us more on why you need to make so many discrete copies? Maybe there is another solution.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[sxschech]

The file is updated once a week, so normally a backup copy is made once a week. However, sometimes I need to update (make changes to code/forms, etc) the file and have the habit of looking at the modified date to determine the age of the file. So if I see that the file has a newer date I may not be sure if it is the version I want to restore or get objects from should something happen. I suppose I could have the backup done during that week, but it seems easier to tell someone to click on the backup button before you click on import data etc. than telling them, don't forget before you leave on Friday to open the file and do a backup. I guess I'll need to accept that I should ignore the file date for these particular backups.

 

[MasterRacker]

Yeah, I think you're stuck with relying on the date built into the backup name. If you're storing the backups in a different folder than the production DB you shouldn't have to worry about users opening and modifying a backup.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[jges]

I'm not entirely sure it is possible, but what if you use one of your form's unload or close event to trigger the backup code? It could check the day of the week and only make a backup on Fridays. No intervention required by your users and the modified date should be correct.

 

[sxschech]

Hi jges,

That's an idea, but these files are not necessarily opened everyday, so if the file doesn't even get used on Friday, the code wouldn't get executed. I suppose I could add a prompt to backup the file on exit, but that would become annoying.

 

[MasterRacker]

Try this:
[ul]
[li]On shutdown, store the date in the DB.[/li]
[li]On Startup, if the last shutdown is the previous Friday or earlier (or whatever your criteria is), run a backup.[/li]
[li]After the fact change the last modified date on the backup to the last shutdown date using the code linked below[/li]
[/ul]

This is Excel VBA but should work the same:

http://www.mrexcel.com/forum/showthread.php?t=86056

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[sxschech]

Thanks for the link, I'll look at it tomorrow and let you know how it goes.

 

[sxschech]

MasterRacker,

Thanks for the tip and link. I was able to use the code from the mrexcel thread to perform the date adjustment. I also found code on that site to grab the "existing" dates of the file. Turns out that when I open the file, even though the modified date will change, it still shows the original modified date (I guess until I close, backup or make changes to the file), so what I did was before doing the actual backup, get these dates rather than have to save them to a table.

I waited until today to verify since today's date and yesterday's date are definitely not the same. When I ran my backup procedure today, it used 15-JULY-2010 as the modified date rather than 16-JULY-2010, and that is what I wanted.

Here is the "foo" part with the modification and the link to where I found it.

Sub foo(bakpath As String, bakname As String)
Dim i As Long

'''''''''''''''''''''''''''''''''
'[URL unfurl="true"]http://www.mrexcel.com/forum/showthread.php?t=73458[/URL]
'Code to grab last modified, created and accessed dates
'''''''''''''''''''''''''''''''''
Dim oFS As Object
Dim datModified As Date
Dim datCreated As Date
Dim datAccessed As Date

Set oFS = CreateObject("Scripting.FileSystemObject")
datCreated = oFS.GetFile(CurrentDb.NAME).DateCreated
datModified = oFS.GetFile(CurrentDb.NAME).DateLastModified
datAccessed = oFS.GetFile(CurrentDb.NAME).DateLastAccessed
Debug.Print datCreated
Debug.Print datModified
Debug.Print datAccessed
'''''''''''''''''''''''''''''''''
On Error GoTo ERR_HANDLER

'i = AdjustFileTime("c:\test.mdb", CDate("12/12/2004 20:09:26"), CDate("11/12/2004 20:09:26"), CDate("10/12/2004 20:09:26"))
i = AdjustFileTime(bakpath & bakname, datModified, datCreated, datAccessed)
Exit Sub
ERR_HANDLER:
MsgBox Err.Description
On Error GoTo 0
End Sub

 

[MasterRacker]

Glad it worked.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[sxschech]

As a follow up...

The code worked fine for one of the files, but not the other. In looking at the form, the other file has code in the ON LOAD event which would be considered a modification by Access and thus the modification date is automatically changed before the form becomes available to run the backup. For this file, I added a text box on the main form and ran code to capture the modified date in the onload event before the code that causes the modification date to be changed happens. Then I had to add an if statement to "foo" and have it substitute the text box modified date if the actual modified date > than the text box's date.

 

[MasterRacker]

I assume you have a startup form specified. Maybe you could create an AutoExec macro to run the backup then open the main form instead.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[sxschech]

Thanks for the suggestion, however, I had solved the problem. I was merely explaining what I did to solve it since the prior code to use the modified date was altering the modified date by the on load event before the backup button could be clicked. By capturing the date and temporarily storing it on the text box of the form, I had it available for use when the backup code button would be clicked. I don't want the backup to run automatically because there are some other activities that occur during the backup process and would rather not have to rewrite the code to separate out these other activities. Also in this particular case I prefer to able to decide when to kick off the backup.

While we're on the subject though, do you think there is a direct way to grab the dates rather than having to dim and set file system object? (Such as seeing the date in the immediate window without having already created the sub) Or would I need to set a reference to this database or copy the code to other databases to be able to view the dates.

 

[TheAceMan1]

How are ya sxschech . . .

In a seperate table, save the new filename along with the [blue]date & time![/blue]. The time stamp reveals your history very easily.



See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[sxschech]

Hi Aceman1,

The last question was mainly an interest to see what access says the file date was of an actual file by looking at its date from the immediate window much as I can see the name of a file for example: ? Currentdb.Name

so what I was wondering was being able to something like this

? FILENAME.DateLastModified

but since this part of the question was only a curiosity, for checking on files other than the current db or the file I'm backing up, just for use in a generic purpose to see what the date was.

 

[sxschech]

Hi Aceman1,

In thinking about the table method to store the date/time. What event would I use to trigger when to change this date. I would like to store the date of the last modified. In Access,the fact that the database is opened changes the date, while in Excel, only when you save the file, does the date change. I wouldn't necessarily be able to use the date I would like to store be updated in the on close event of the database, as this wouldn't always be a change. The date would need to be stored only when an actual change takes place, either to the code, sql statement or adding/edit data. But here's the tricky part of this, since a user can run reports, I would not consider this a modification (there is code that copies or modifies where clauses to queries and reports).