Backup a password protected backend 1

[gerard1979]

I want to back up my backend database by code:
I wrote (with help of tekTips) the code below.
But the function failed on the line
fso.CopyFile strSource, strDest, True
Can the cause be my PROTECTED backend
The error is Number 52,

Thnx in advance

Public Sub BackUp()
'This function backs up to the c: drive, you can back up to any location, just change the strDest Value

On Error GoTo Err_Backup
Dim db As Database
Dim strSource As String, strDest As String, strError As String
Dim strDate As String, strDateX As String
Dim fso As FileSystemObject

If Bevestigen("Wilt u een back-up van de data maken?&quot Then
' Un-comment the following 3 lines for a new backup for every day
strDate = Format(Date, "mm/dd/yy&quot
strDateX = Left(strDate, 2) & Mid(strDate, 4, 2) & Right(strDate, 2)
strDest = "C:\" & strDateX

Set db = CurrentDb()
DoCmd.Hourglass True

'Put any table name in here that exists in your back-end
strSource = db.TableDefs("CONTACTPERSOON&quot.Connect
strSource = Mid(strSource, 11, Len(strSource) - 10)

' If you are using a new back-up every day, un-comment this line and replace the database name, and comment
'out the next line down
strDest = strDest & "_BackUpAcquisitie_be.mdb"


Set fso = New FileSystemObject
fso.CopyFile strSource, strDest, True

db.Close
Set fso = Nothing

DoCmd.Hourglass False
BerichtWeergeven ("Backup Compleet.....&quot
Dim emailTo As String, strOnderwerp As String, strBericht As String
emailTo = HaalEmail("Gkr&quot
strOnderwerp = "backup van data gemaakt"
strBericht = ""
strBericht = strBericht & "Datum: " & Date & vbNewLine
strBericht = strBericht & "Een kopie van de data is weggeschreven naar " & strDest & vbNewLine
strBericht = strBericht & "Er hebben zich geen fouten voorgedaan......."
fctnOutlook EmailCRM, emailTo, , , strOnderwerp, strBericht, , , False

End If

Exit_Backup:
Exit Sub

Err_Backup:
Select Case Err.Number
Case 61
strError = "Schijf is vol" & vbNewLine & "Kan geen backup maken"
MsgBox strError, vbCritical, " Disk Full"
Kill strDest
Case 71
strError = "Geen schijf in de Zip-drive" & vbNewLine & "Voer schijf in, aub"
MsgBox strError, vbCritical, " No Disk"
Case Else
Err.Raise Err.Number, Err.Description
End Select
DoCmd.Hourglass False
Resume Exit_Backup
End Sub

 

[StarPassing]

Add code like below , to see if you can get an exclusive lock on the back-end before backing up. If not, something is still open between the front and back-end.

Function IsExclusive(PathToMdb) As Integer
' Code Courtesy of
' Dev Ashish

' this function will report wheither or not the database can be opened exclusive for backup
' it will return True if you can get an exclusive lock
Dim hFile As Integer
hFile = FreeFile

On Error Resume Next

Open PathToMdb For Binary Access Read Write Lock Read Write As hFile
Select Case Err
Case 0 ' OK no errors
IsExclusive = True
Case 70 'Permission denied
IsExclusive = False
Case Else ' some other error
IsExclusive = False
End Select
Close hFile
On Error GoTo 0
End Function

 

[gerard1979]

Thnx for ur code StarPassing, I will try this.
But the trick of my code was, that we can backup EVEN when the backend is in use and THAT's NICE and what I want.

Any other ideas recommended,
Thnx in advance,
Gerard

 

[danvlas]

I think you will get error 70 (permission denied) if you try to copy any file that is in use.
It happens to me when I try to automatically back up some excel files created by others.
It's a system thing, not Access...
HTH


Daniel Vlas
Systems Consultant

 

[gerard1979]

Thanx for your reaction Daniel.
The problem is, before I password protect my backend The function as I wrote above is working perfectly. (U can try it by yourself), but now I password protect my backend and error 52 come up.
The problem is in the FileSystemObject, maybe someone has more detailed information about this feature.

Thnx in advance for further reactions
Gerard

 

[danvlas]

?AccessError(52)
Bad file name or number

which determined me to test your code.
I got some compilation errors as I don't have some of your functions (and I don't use FileSystemObject), changed your code a little and came up with this:

Public Sub Backup()
'This function backs up to the c: drive, you can back up to any location, just change the strDest Value

On Error GoTo Err_Backup
Dim db As Database
Dim strSource As String, strDest As String, strError As String
Dim strDate As String, strDateX As String
'Dim fso As FileSystemObject

If MsgBox("Wilt u een back-up van de data maken?", vbYesNo) = vbYes Then
' Un-comment the following 3 lines for a new backup for every day
' strDate = Format(Date, "mm/dd/yy&quot
' strDateX = Left(strDate, 2) & Mid(strDate, 4, 2) & Right(strDate, 2)
strDest = "C:\" & Format(Date, "mmddyy&quot

Set db = CurrentDb()
DoCmd.Hourglass True

'Put any table name in here that exists in your back-end
strSource = db.TableDefs("chelt").Connect

'This is wrong!!!!!!!
' strSource = Mid(strSource, 11, Len(strSource) - 10)
'It returns:
'PWD=mypassword;DATABASE=C:\Path\File.mdb


This works fine:
strSource = Right(strSource, Len(strSource) - InStr(strSource, ";DATABASE=&quot - 9)

' If you are using a new back-up every day, un-comment this line and replace the database name, and comment
'out the next line down
strDest = strDest & "_BackUpAcquisitie_be.mdb"


'Set fso = New FileSystemObject
FileCopy strSource, strDest

db.Close
'Set fso = Nothing

DoCmd.Hourglass False
MsgBox "Backup Compleet....."

End If

Exit_Backup:
Exit Sub

Err_Backup:
Select Case Err.Number
Case 61
strError = "Schijf is vol" & vbNewLine & "Kan geen backup maken"
MsgBox strError, vbCritical, " Disk Full"
Kill strDest
Case 71
strError = "Geen schijf in de Zip-drive" & vbNewLine & "Voer schijf in, aub"
MsgBox strError, vbCritical, " No Disk"
Case Else
Err.Raise Err.Number, Err.Description
End Select
DoCmd.Hourglass False
Resume Exit_Backup
End Sub


However, if the back end is open, you will get the error 70...

HTH


Daniel Vlas
Systems Consultant

 

[danvlas]

And...on a second thought, you can avoid hard coding the name of a table by just inspecting MSysObjects table for linked tables (type 6), Database column... That will spare you from Instr, Right, Left and so on...

Just a thought...


Daniel Vlas
Systems Consultant

 

[gerard1979]

Great,
ur code from strSource is completely right,
stupid me!! of course Access is saving the password in the stringsource.

Thnx again,

Deserve ur star!!
Gerard