How do I backup (and compact/repair)

[Pharma Down]

My database seems huge and quite a few people use it... so I guess that I should regularly back up the data and compact/repair. Is there a simple way of doing this or do I have to open up the database in design mode each time and click 'Save As' (with a backup filename)?

(Database designed/built in MS Access 2003, but usually run through 2010)

 

[pr2-eugin]

I have selected 'Compact & Repair on close ' option selected. And written code to backup my tables in EXCEL format. So every morning, I just open the Master Database (with table) and it will automatically Export tables, then Close (so Compact is performed).

Compact & Repair does not have to be performed every single day, unless it really does grow exponentially in one day.

 

[CJ_London]

I'm assuming you have front ends on user machines and the backend on the network.

A backup is just a copy, so you can just copy the backend to an archive directory. But I would have thought your server would be doing daily backups anyway although there is no harm in taking additional action. The front end should not change so should not need backing up although I would keep a 'master copy' available so in the event the user copy is deleted or for some reason becomes unusable it can easily be replaced.

With compacting, the front end should not change, so compacting is not required (I assume it is compacted before being released). To compact the backend there are a couple of options:

• You can go into the backend and click on the compact option
• Or you can set the backend 'compact on close' property to true so just open the backend then exit (but don't really recommend this option)
• Or you can write some VBA code in another db (perhaps the front end) to go behind a button so when clicked it will remotely compact the backend.

Whichever way you go, compacting can only be done when there are no other users linked to the backend so it tends to be an evening job (so perhaps incorporate a timer in the third option above).

 

[Pharma Down]

OK...
So it can be done!
My database contains 152 tables, plus queries, forms and reports. When I set up the security features it split into front and back ends.

In terms of having a front end on each machine... my team is pretty mobile and might end up using a different person's machine (not part of our dept), so I decided to leave front and back ends in the same place, but provide individuals with shortcuts to open up the database from their personal machines. The database is hosted on a shared network drive. Was this the wrong thing to do?

Yes, our IT dept do create their own backups, but I have experienced a problem with the database and IT are not resolving the issue very quickly - I felt that if I had my own backups I would still have access to the data if needed (then IT would be the last backup backup!).

So... you have both suggested adding a 'bit of code', but please can you elaborate. If I only need to backup the tables (where the data is stored) what is the best way to do it and what code would I need?
(NB: my programming skills are negligible)

Thanks
Andy

 

[CJ_London]

so I decided to leave front and back ends in the same place, but provide individuals with shortcuts to open up the database from their personal machines. The database is hosted on a shared network drive. Was this the wrong thing to do?

It is better to have front ends on individual machines, if it gets corrupted then all users will be unable to use it. But I can see your problem and can't suggest an alternative

what code would I need

Behind a button on the onclick event you have on a form somewhere put this code- change the bits in red:

Private Sub myButton_Click()
'creates an archive copy of both uncompacted and compacted db's as well as compacting

Dim BE As DBEngine
Dim BackendPath as String
Dim ArchivePath as String
Dim BackendName as String

'note: better not to use mapped drive references as they may not be the same on each machine
BackendPath = "\\ServerName\DirectoryName\"
ArchivePath = "\\ServerName\DirectoryName\Archive\"
BackendName="Backend.accdb"
Set BE = Application.DBEngine

On Error Resume Next
Kill ArchivePath & BackendName 'delete old archive copy
Kill ArchivePath & "CP_" & BackendName 'delete old archive compacted copy
FileCopy BackendPath & BackendName, ArchivePath & BackendName 'copy uncompacted backend to archive
BE.CompactDatabase ArchivePath & BackendName, ArchivePath & "CP_" & BackendName 'compact archive copy to archive directory
Kill BackendPath & BackendName 'delete the working copy of the backend
FileCopy ArchivePath & "CP_" BackendName, BackendPath & BackendName 'copy compacted file to working copy
Set BE=Nothing
End Sub

 

[Pharma Down]

Without wishing to appear too stupid, please can you repost the text above and show me the bits that I need to edit to make it work for my database!

Also, through trying to make sense of what you have written it looks as though this code creates a backup and deletes any other backups (is that correct, or just my lack of understanding?). I'm happy to keep old backups and might need to - can that be changed?

 

[CJ_London]

show me the bits that I need to edit

Have edited my original post - change the bits in red

code creates a backup and deletes any other backups

It only deletes the backend backup in the archive directory with the same name, if you want to keep all backups, suggest you either them in another directory which you can do by adding the following code to just before the Set BE=Nothing:

FileCopy ArchivePath & "CP_" BackendName, BackendPath & "Backups\" & BackendName & " " & format(Now(),"yyyymmdd hhmmss") 'copy compacted file to backup
Set BE=Nothing

This assumes you have a folder in your backend path called Backups

 

[dijilator]

Will this procedure run if the backend is locked (open) by another user on a network?

 

[CJ_London]

Please read the thread - the answer is there

 

[dijilator]

Whichever way you go, compacting can only be done when there are no other users linked to the backend so it tends to be an evening job (so perhaps incorporate a timer in the third option above).

I missed the inference about adding the timer that this code then does not have a line to stop it from running if the lock file is open. Thanks for referring me back to the post.