Autobackup

[Legends77]

Tried using "CopyDatabaseFile" but get the error of not avilable at this time.

I have been directed to make a certain DB public, but I would like to have a backup, so I was looking for a way to have a back up created each time someone opens the database.
Copy the database to my "P:\DBBackup\C+WIP\FILENAMETIMESTAMP"
So that I can "track" and see where / if any changes happen.
I have tried using security but so many people and different levels, seems this would be the fastest way.

 

[PHV]

For me, DoCmd.CopyDatabaseFile is only meaningfull in an .adp ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Legends77]

Okay, since it seems that the macro command will not do me too much good, any suggestions on making a backup of the database that does not require manually copy / pasting?

 

[kjv1611]

Any of the various free 3rd party back-up apps should do. SyncBack works really well for automated/scheduled backups, I think. You can backup locally or over the network.

 

[Legends77]

Bad news... I am not able to download - security. Any other thoughts or suggestions?

 

[bubba100]

How about a bat file?

 

[Legends77]

How would that work? My bat experience is very limited and long ago. I am almost thinking to split the database, made the FE a mde file. Then have the FE on each computer compare something, like a version number or letter, from something on the FE to the BE and upload a new FE it they do not match. This way, if I update the FE on the server, the user does not need to do anything but simply open the DB to get the new FE.
Only reason I think of this is because someplace where I use to work had a microsoft database that did something similar.
This avoids the possiblity of them "getting into the data" as the BE will be on the server and I can "hide" it and the FE will be a mde read only file.
This sound crazy?

 

[bubba100]

Take a look at thread181-844184 for a discussion of FE-BE.

We update our FE as the need requires and distribute with a bat file. There are numerious ways to do this, sense ours is "as needed" we send an e-mail telling those involved to goto a Common folder and open the bat file found in folder "X". It copies the updated file totheir machine. There are other ways found at this site and they work for them. Good luck.

 

[kjv1611]

Well, if you can't download 3rd party apps, if you want to do the batch file deal, you can also do it via VBA. I think it is possible within the same database, just not as easy as using an external database. I know I've seen such code before, and maybe even used it several years ago..

 

[Nordyck]

I tried a bat process and it didn't work reliably.

I had to backup and compact the db every night, a trick I used when the last user closed the database (check the ldb file or keep track of who is logged on), it would kick off an exe file. The exe would then compact the db and create a zip of the backup. There were other constratins that I added such as keeping the last 5 days of backups, version checking between the FE/BE, etc.


nordycki@hotmail.com

 

[kjv1611]

Nordyck,

Can you provide a little more detail if you're making a suggestion? Saying "when the last user closed the database.." - how? "... .exe file." What .exe file? A batch file? Some custom program, what?

 

[Nordyck]

the code was written in vb6. When the user open the app (FE) he's automatically logged on via a hidden form when the app opens. a checkbox by his userid is automatically checked and date when he logs on. Then when he closes the app, he is automatically signs out and date cleared. On close of the hidden form, the exe file is executed using shell command provided he was the last person. The date is incase the user aborted the program instead of closing it.

Once the exe file is kicked off via and it will perform different functions specified by the commandline.

nordycki@hotmail.com

 

[Nordyck]

provide more clarification, depending upon your network permissions, the exe (written in vb6) file could be on the local PC, as in my case, or executed from the network. The file size <40kb. The FE (front end), captured the network logon ID for automatic logon. With this process, I also denied access for unapproved users.

The exe handled installing new version of the FE, zipped backups, compaction and various other tasks. All executed via commandline.



nordycki@hotmail.com

 

[avayaman]

Somewhere on here was code posted that will create a backup on close down. I integrated it into my database. It wiull create a folder called backups in the same directory that the database is in and create backups under the current date. i simply created a new close button on the switchboard to execute it.

Private Sub Command150_Click()

Call BackupFrontEnd

CloseCurrentDatabase

End Sub


Public Function BackupFrontEnd()

Dim dbs
Dim tdfs
Dim fso
Dim strCurrentDB
Dim intExtPosition
Dim strExtension
Dim intExtlength
Dim strBackupPath
Dim sfld
Dim strDayPrefix
Dim strSaveName
Dim strProposedSaveName
Dim strTitle
Dim strPrompt
Dim SaveNo
Dim rst

On Error GoTo ErrorHandler

Set dbs = CurrentDb
Set tdfs = dbs.TableDefs
'Components of the FileSystemObject object library are used
'to work with files
Set fso = CreateObject("Scripting.FileSystemObject")
strCurrentDB = Application.CurrentProject.Name
Debug.Print "Current db: " & strCurrentDB
intExtPosition = InStr(strCurrentDB, ".")
strExtension = Mid(strCurrentDB, intExtPosition)
intExtlength = Len(strExtension)

'Create backup path string (Backups folder under database folder)
strBackupPath = Application.CurrentProject.Path & "\Backups\"
Debug.Print "Backup path: " & strBackupPath

'Check whether path is valid
On Error Resume Next

Set sfld = fso.GetFolder(strBackupPath)
If sfld Is Nothing Then
'Create folder
Set sfld = fso.CreateFolder(strBackupPath)
End If

On Error GoTo ErrorHandler
'Create proposed save name for backup
strDayPrefix = Format(Date, "d-mmm-yyyy")
strSaveName = Left(strCurrentDB, _
Len(strCurrentDB) - intExtlength) & " Copy " & SaveNo _
& ", " & strDayPrefix & strExtension
strProposedSaveName = strBackupPath & strSaveName
Debug.Print "Backup save name: " & strProposedSaveName
strTitle = "Database backup"
strPrompt = "Save database to " & strProposedSaveName & "?"
strSaveName = Nz(InputBox(prompt:=strPrompt, _
Title:=strTitle, Default:=strProposedSaveName))

'Deal with user canceling out of the InputBox
If strSaveName = "" Then
GoTo ErrorHandlerExit
End If

Set rst = dbs.OpenRecordset("zstblBackupInfo")
With rst
.AddNew
![SaveDate] = Format(Date, "d-mmm-yyyy")
![SaveNumber] = SaveNo
.Update
.Close
End With

fso.CopyFile Source:=CurrentDb.Name, _
destination:=strSaveName
Msgbox "A backup with todays date should have been saved in the backups subfolder"

ErrorHandlerExit:

Exit Function

ErrorHandler:
Msgbox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Function

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[avayaman]

BTW you can copy & paste the above code verbatim, no need to change anything. I left the original close button on the switchboard as well in case I did not want to backup on close.

It works fantastic. No more worries about corruption, I can simply recover the last time I went in.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com