Date Time Stamp ? Restore/Backup?

[authorsami]

I have a code that will create a back up of my backend tables. The code creates a file name which includes the date and time the backup was created.

My question is can I in this code add something that will put this date and time in a field in a table. I want to use this date and time field in an append query when I do a restore of this data to a database at a different location.

Is what I want to do possible???? I hope I explained it correctly.

Sharon

 

[Cosmos75]

authorsami,

Very interesting!

How are you creating a back-up of your tables with code? I've been using a database created by someone else that creates backups but does not include the date and time the backup was created.

Is this code something you can share? (Hope the answer is yes!)

 

[authorsami]

Backup code

Cosmos,

I got the code from someone on tek-tips. I will post it below. If you send me your email I will send you the email I got so you can see the additional notes he gave me.

sharon@entechcomputers.com

-------
Option Compare Database
Option Explicit

Private Const FO_COPY = &H2

Private Const FOF_SIMPLEPROGRESS = &H100

Private Const FOF_NOCONFIRMATION = &H10


Private Declare Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" _
(lpFileOp As SHFILEOPSTRUCT) As Long


Private Type SHFILEOPSTRUCT
hWnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type


Dim x As SHFILEOPSTRUCT


Private Sub Command0_Click()
On Error Resume Next
Dim fso1
Dim FileNameX As String
Dim strFilter As String
Dim strInputFileName As String
Dim StrsaveFileName
Dim filex As String
Set fso1 = CreateObject("Scripting.FileSystemObject")
fso1.CreateFolder ("C:\SharonTest\") 'This is the default directory where the file will be saved
FileNameX = "TestFile" & Month(Now()) & "-" & Day(Now()) & "-" & Year(Now()) & "_" & Hour(Now()) & "_" & Minute(Now()) & "_" & Second(Now()) 'This is the default filename with a date time stamp
strFilter = ahtAddFilterItem("Export ", "Access Files (*.mdb)", "*.mdb")
StrsaveFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY, "C:\SharonTest\", strFilter, , , FileNameX, , , False)


filex = StrsaveFileName


x.pFrom = "C:\Backend.mdb" 'This is the location of your backend mdb.
x.pTo = StrsaveFileName
x.fFlags = FOF_NOCONFIRMATION
x.wFunc = FO_COPY
SHFileOperation x
MsgBox "Backup Complete", vbOKOnly, "Status"
End Sub
Private Sub Command61_Click()
On Error GoTo Err_Command61_Click


DoCmd.Close

Exit_Command61_Click:
Exit Sub

Err_Command61_Click:
MsgBox Err.Description
Resume Exit_Command61_Click

End Sub

 

[Sorrells]

authorsami,

Did you figure it out? I have used the same technique to save important data tables to a backup mdb on either a floppy or a hard drive subfolder. The tables that are saved have a suffix given of "mm/dd/yyyy".

Now, I am looking at a restore and am about clueless! I am thinking that I should display a list box with the parsed dates so that the user can select the backup date they want to restore.

The question is how do I get these dates in a listbox? What approach did you take?

I have shied away from arrays but I also know that I can obtain strip the string dates off of the table names using the TableDef object. I wonder if I could concatenate them into one large comma delimited string then assign this as a Value List for the listbox.

If the user selects one, I can take the string selected and then return to the backup dB to restore the appropriate tables.

I think I'll play with this for a while and hope that I hear from you.

 

[Sorrells]

Such happiness! I've got it, a populated listbox!

' FUNCTION TO POPULATE LISTBOX WITH BACKUP DATES
' Open the backup dB on the selected medium.
' Extract the date suffixs from one of the tables saved -Client_Info
' Concatinate the dates into the variable Value_List
' Assign this to the ControlSource of the listbox
'
'
Private Function Get_Backup_Dates()
Dim tdf As TableDef
Dim Tbl_Counter As Integer
For Each tdf In dbsBackup.TableDefs
With tdf
'MsgBox .Name 'debugging tool
If Left(.Name, 11) = "Client_Info" Then
Value_List = Value_List & Mid(.Name, 12) & ";"
End If
End With
Next tdf

lstBackup_Dates.RowSource = Value_List

Set tdf = Nothing

End Function

 

[Sorrells]

Restore Tables - Primary Keys & Relationships

Greetings,

I am going to continue this thread, as it is the only one I found with both Backup and Restore in the title.

Continuing on with the restore, this morning I discovered that my backup of tables into another dB was causing the stripping of the primary keys. I am using the TransferDatabase Method and do not see any option for forcing the keys to be retained. This means that upon restore, I have to reset the key for each of the 5 tables.

Then with some manual activity, I also learned that the table relationships are broken when an imported table replaces a current one in the database being restored. Thus I have to rebuild the relationships (referential integrity, cascading deletes).

There may be issues around the data database (back end) being open, providing linked tables to the program dB. Will I be refused access to manipulate its tables? I haven't looked at that.

I don't have any answers at this time. It will be my day's objective to make some progress on this. Any suggestions on design approach will be greatly appreciated.

 

[Tim L]

Hi Sorrels,

I too am 'playing around' with backing up and restoring, although I have taken a different route: I save a copy of the table (or partial copy if needed) using the TransferText function.

When I import the data I first load it into a spare table (almost a carbon copy, bar the relationships, of the table that is being restored).

I then use an Update Query to add the imported data to the original table. I actually pass the data through a middle-man query first though, one that weeds out any potential conflicts on the Primary Key front. Of course if there are no records in your main table to start with then all of the imported records will be appended to the main table.

Because the main table is not actually replaced none of the relationships get broken, no keys need to be set and it all works quite smoothly (shame about other aspects that I am still working on...).

Perhaps this method could be of some use to you.

HTH

Tim

 

[Sorrells]

Tim,

Your comments are well taken. I just read in another forum that there is no problem appending records to tables with an AutoNumber as the primary key. The author stated that the field would accept the numbers being supplied to the table and would then increment from the highest number. I have not tested this but a lot of my sense for a need to replace the table stems from difficulty with updating records in a table with AutoNumbers, of which all my data tables have.

I can see that, for example, deleting the records of the table, then appending the 'restored' records should leave the table relationships and primary keys unaffected. Very good! I'll work with this a while and get back with my results.

Thanks for the tip Tim, that is what I was looking for!

 

[Tim L]

Glad that I could be of help.

With regards to the autonumber Primary Key: I can see the logic behind Access accepting the numbers coming in via an update query - just so long as none are likely to duplicate any that are already in the database. If you are doing a full restore then this problem is not likely to occur, although if you are doing a partial restore the a bit more care would probably need to be taken.

Anyway, good luck and let us know how it all goes.

Tim