MS ACCESS DATABASE BACKUP (1 Viewer)

[Kamayo Ako]

I have button to backup the database with local table it working fine with the standalone.
However if i split the database and do same process click backup button but now i have some issue, It backup only the frontend table
which shortcut instead of suppose local table or backend.

Thank you for you support.

 

[ebs17]

I have button ...

It would certainly make more sense to look at the code used behind it. The button is just the event trigger for calling an action.

backup the database

... would change to backend backup. To be on the safe side, you should only do this if you have exclusive access to the backend, i.e. there is no further access from you or from third parties.

 

[arnelgp]

there is a free code here:
autobackup database | Access World Forums (access-programmers.co.uk)

 

[Kamayo Ako]

It would certainly make more sense to look at the code used behind it. The button is just the event trigger for calling an action.


... would change to backend backup. To be on the safe side, you should only do this if you have exclusive access to the backend, i.e. there is no further access from you or from third parties.

Function RunSub()
BackUp
End Function

Sub BackUp()
Dim dTime As Date
On Error Resume Next
dTime = InputBox("Create a backup at", , Time + TimeValue("00:00:05"))
If err.Number <> 0 Then Exit Sub
Do Until Time = dTime
DoEvents
Loop
MsgBox "Time to create a backup"

Dim sFile As String, oDB As DAO.Database
sFile = CurrentProject.Path & "\" & Format(Date, "m-d-yy") & ".accdb"
If Dir(sFile) <> "" Then Kill sFile
Set oDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)
oDB.Close
DoCmd.Hourglass True

Dim oTD As TableDef
For Each oTD In CurrentDb.TableDefs
If Left(oTD.name, 4) <> "MSys" Then
DoCmd.CopyObject sFile, , acTable, oTD.name
'OR: DoCmd.TransferDatabase acExport, "Microsoft Access", sFile, acTable, oTD.Name, oTD.Name
End If
Next oTD


DoCmd.Hourglass False
MsgBox "Backup is stored in the same folder"
End Sub

 

[Kamayo Ako]

there is a free code here:
autobackup database | Access World Forums (access-programmers.co.uk)

Na try ko siya sir gumagana siya pero pagnaka split database siya yung frontend lang yung na backup. Kahit yung backend lang sana. Shortcut lang ang ma save sir need mo pa convert to local table.

 

[arnelgp]

try mo ito:

Function RunSub()
    BackUp
End Function

Sub BackUp()
    Dim dTime As Date
    Dim sFile As String
    Dim sSrcDb As String
   
    On Error Resume Next
    dTime = InputBox("Create a backup at", , Time + TimeValue("00:00:05"))
    If Err.Number <> 0 Then Exit Sub
    Do Until Time = dTime
        DoEvents
    Loop
    MsgBox "Time to create a backup"
   
    Dim oDB As DAO.Database
    sFile = CurrentProject.Path & "\" & Format(Date, "m-d-yy") & ".accdb"
    If Dir(sFile) <> "" Then Kill sFile
   
    '!!!!!!
    ' arnelgp
    ' CHANGE "data" with any linked tablename from your FrontEnd
    '
    sSrcDb = BackEndPath("data")
   
    ' open the backend
    '
    ' note, no password
    Set oDB = OpenDatabase(sSrcDb, False, False)
 
   Call DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)

   
    DoCmd.Hourglass True
   
    Dim oTD As TableDef
    For Each oTD In oDB.TableDefs
       
        If Left(oTD.Name, 4) <> "MSys" Then
            DoCmd.CopyObject sFile, oTD.Name, acTable, oTD.Name
            'OR: DoCmd.TransferDatabase acExport, "Microsoft Access", sFile, acTable, oTD.Name, oTD.Name
        End If
    Next oTD
   
    oDB.Close
    Set oDB = Nothing
   
    DoCmd.Hourglass False
    MsgBox "Backup is stored in the same folder"
End Sub


Public Function BackEndPath(ByVal strLinkedTable) As String
    Dim db As DAO.Database
    Dim con As String
    On Error GoTo err_handler
    Set db = CurrentDb
    con = db.TableDefs(strLinkedTable).Connect
    BackEndPath = Mid$(con, InStr(con, "=") + 1)
exit_function:
    Set db = Nothing
    Exit Function
err_handler:
    Debug.Print Err.Number
    Resume exit_function
End Function

 

[ebs17]

DoCmd.CopyObject sFile, , acTable, oTD.name
'OR: DoCmd.TransferDatabase   acExport, "Microsoft Access", sFile, acTable, oTD.Name, oTD.Name

It backup only the frontend table
which shortcut instead of suppose local table or backend.

Application.DoCmd...

The DoCmd object is an object of the Access application object. If nothing else is specified, it will be executed where the code is running, i.e. in the frontend. In the frontend there are only links to the tables.

' for example, use an Application object on the backend

Dim oAcc As Access.Application
Set oAcc = CreateObject("Access.Application")
With oAcc
    .OpenCurrentDatabase "c:\...\Backend.accdb"

    .DoCmd...

    .CloseCurrentDatabase
End With
oAcc.Quit
Set oAcc = Nothing

 

[theDBguy]

Just in case this is useful...

Code Snippets

 

[ebs17]

oFSO.CopyFile strSource, strDestination

... is not interested in third-party access. Without exclusive access, copying can occur while active writes are taking place in the backend, with the result that a copy is made but this copy is corrupted and therefore unusable as a backup.

VBA.FileCopy would be more useful here.

 

[theDBguy]

oFSO.CopyFile strSource, strDestination

... is not interested in third-party access. Without exclusive access, copying can occur while active writes are taking place in the backend, with the result that a copy is made but this copy is corrupted and therefore unusable as a backup.

VBA.FileCopy would be more useful here.

Hi. You're probably right. I didn't come up with that code; however, I hadn't encountered any issues using it. Maybe I'm just lucky. Can you post a link pointing to some information about the difference between using fso.CopyFile and VBA.FileCopy? Thanks!

 

[moke123]

Hi. You're probably right. I didn't come up with that code; however, I hadn't encountered any issues using it. Maybe I'm just lucky. Can you post a link pointing to some information about the difference between using fso.CopyFile and VBA.FileCopy? Thanks!

I've been using Brent's code for years and never had any issues either.

 

[ebs17]

never had any issues either

As I said, copying works.
The question is whether you can do something with the copy in case of need/emergency.
I think you also want to be safe with a backup copy.

Can you post a link

No, but you can easily test it yourself.
Try copying an open file using both methods.
You do not get exclusive access to a file that is already open. Therefore, the method with exclusive access will react here with a runtime error.

 

[theDBguy]

No, but you can easily test it yourself.
Try copying an open file using both methods.
You do not get exclusive access to a file that is already open. Therefore, the method with exclusive access will react here with a runtime error.

I was just curious, so I gave it a shot. Assuming I understood what you meant by "open file," here's what I did.

1. I added the aforementioned backup function to a split database
2. I opened the FE and observed the lock file for the FE show up in the folder
3. I opened a form and started to edit a record
4. I observed the lock file for the BE show up in the folder at this point
5. While leaving the form Dirty (still in edit mode and not saved yet), I proceeded to execute the new backup function
6. I observed the BE backup file show up in the folder
7. I closed the form and then closed the FE, I observed all the lock files disappear from the folder
8. I opened the BE backup file and verified all the data was intact
9. Now, I would admit that the backup file does not contain the changes I was in the middle of making to a record when I executed the backup, but at least the backup file was not corrupted and still very usable
10. I replaced fso.CopyFile with VBA.FileCopy and received the "Permission denied" error
11. 
    
    I think I understand what you mean now. I agree that a full backup can only be made when no one else is in the database, but that's exactly the reason why I posted that function in the first place. Many users asked for a way to create a backup even when people are using the database - just as safety precaution to make sure they have some sort of an incremental backup before they can or need to create a full backup.
    
    You just scared me when you said the backup file created this way would be corrupt. I hope that doesn't happen. Cheers!

 

[ebs17]

Data backups in ongoing multi-user operation: You can also think of an active database management system.

 

[Kamayo Ako]

try mo ito:

Function RunSub()
    BackUp
End Function

Sub BackUp()
    Dim dTime As Date
    Dim sFile As String
    Dim sSrcDb As String
  
    On Error Resume Next
    dTime = InputBox("Create a backup at", , Time + TimeValue("00:00:05"))
    If Err.Number <> 0 Then Exit Sub
    Do Until Time = dTime
        DoEvents
    Loop
    MsgBox "Time to create a backup"
  
    Dim oDB As DAO.Database
    sFile = CurrentProject.Path & "\" & Format(Date, "m-d-yy") & ".accdb"
    If Dir(sFile) <> "" Then Kill sFile
  
    '!!!!!!
    ' arnelgp
    ' CHANGE "data" with any linked tablename from your FrontEnd
    '
    sSrcDb = BackEndPath("data")
  
    ' open the backend
    '
    ' note, no password
    Set oDB = OpenDatabase(sSrcDb, False, False)

   Call DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)

  
    DoCmd.Hourglass True
  
    Dim oTD As TableDef
    For Each oTD In oDB.TableDefs
      
        If Left(oTD.Name, 4) <> "MSys" Then
            DoCmd.CopyObject sFile, oTD.Name, acTable, oTD.Name
            'OR: DoCmd.TransferDatabase acExport, "Microsoft Access", sFile, acTable, oTD.Name, oTD.Name
        End If
    Next oTD
  
    oDB.Close
    Set oDB = Nothing
  
    DoCmd.Hourglass False
    MsgBox "Backup is stored in the same folder"
End Sub


Public Function BackEndPath(ByVal strLinkedTable) As String
    Dim db As DAO.Database
    Dim con As String
    On Error GoTo err_handler
    Set db = CurrentDb
    con = db.TableDefs(strLinkedTable).Connect
    BackEndPath = Mid$(con, InStr(con, "=") + 1)
exit_function:
    Set db = Nothing
    Exit Function
err_handler:
    Debug.Print Err.Number
    Resume exit_function
End Function

Maraming salamat ng sobra sir

 

[Pat Hartman]

You might want to take a look at some of the tools sold by www.fmsinc.com I use the development tools which are very helpful and my clients, when they have ACE BE's use the backup management tool.

 

[Kamayo Ako]

You might want to take a look at some of the tools sold by www.fmsinc.com I use the development tools which are very helpful and my clients, when they have ACE BE's use the backup management tool.

Thank you sir