Archiving/Back up of an access database 1

[TM2]

Hi,<br>I was wondering how to write the code behind a command button that would allow the user to archive/back up the database. It may occur on the same hard drive as the original,but it may not.<br>Any ideas??<br>Thanks for your time

 

[Julio]

Try just compacting it to the dest folder:<br><br><br>DBEngine.CompactDatabase &quot;c:\docs\mydb.mdb&quot;, &quot;d:\bak\mydbcopy.mdb&quot;

 

[Julio]

Try just compacting it to the dest folder:<br><br><br>DBEngine.CompactDatabase &quot;c:\docs\mydb.mdb&quot;, &quot;d:\bak\mydbcopy.mdb&quot;<br><br>HTH<br><br>Julio

 

[Lightning]

Hi TM<br><br>You can't use the CompactDatabase method to compact an open database, so you need a bit more code.<br><br>I've just done this for an application I'm developing, which consists of a front-end db and a back-end db for data (Access 97).&nbsp;&nbsp;Below is the code to compact the front-end database, compact the back-end database, and then back-up the back-end db.&nbsp;&nbsp;I put them in a module, and then run them from a switchboard made with the switchboard manager.<br><br>For more on the Back-up function, check out<br>&nbsp;&nbsp;&nbsp;<A HREF="

http://www.odyssey.apana.org.au/~abrowne/homepage.html"

TARGET="_new">

http://www.odyssey.apana.org.au/~abrowne/homepage.html</A><br><br>Hope

this helps.<br><br>Function DoCompression()<br>'Compact the front-end (open) Database<br>&nbsp;&nbsp;&nbsp;&nbsp;SendKeys (&quot;%(TDC)&quot<br>&nbsp;End Function<br><br>Public Sub Backup()<br>'This function backs up to the g: drive, you can back up to any location,<br>'just change the strDest Value<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSource As String, strDest As String, strError As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strDate As String, strDateX As String<br><br>&nbsp;&nbsp;&nbsp;&nbsp;If MsgBox(&quot;Are you sure you want to back up data?&quot;, vbQuestion + vbYesNo, &quot; Continue with Data Back-Up?&quot = vbYes Then<br>'Un-comment the following 3 lines for a new backup for every day<br>'strDate = Format(Date, &quot;mm/dd/yy&quot<br>'strDateX = Left(strDate, 2) & Mid(strDate, 4, 2) & Right(strDate, 2)<br>'strDest = &quot;a:\&quot; & strDateX<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb()<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass True<br><br>'Put any table name in here that exists in your back-end<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strSource = db.TableDefs(&quot;YourTable&quot.Connect<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strSource = Mid(strSource, 11, Len(strSource) - 10)<br><br>'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<br>'strDest = strDest & &quot;_YourBackEndDBNameHere.mdb&quot;<br><br>'Replace with your database Back-end name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strDest = &quot;g:\PathStatement\Back-Up Files\YourFile.mdb&quot;<br>'Copy the file<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FileCopy strSource, strDest<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db.Close<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass False<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox (&quot;Backup to &quot; & vbNewLine & strDest & vbNewLine & &quot; is Complete&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br><br>Exit_Backup:<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br><br>Err_Backup:<br>'Display appropriate Error Message<br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case Err.Number<br>&nbsp;&nbsp;&nbsp;&nbsp;Case 61<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strError = &quot;Floppy disk is full&quot; & vbNewLine & &quot;cannot export mdb&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox strError, vbCritical, &quot; Disk Full&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Kill strDest<br>&nbsp;&nbsp;&nbsp;&nbsp;Case 70<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strError = &quot;File is open&quot; & vbNewLine & &quot;cannot export mdb&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox strError, vbCritical, &quot; File Open&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Case 71<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strError = &quot;No disk in drive&quot; & vbNewLine & &quot;please insert disk&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox strError, vbCritical, &quot; No Disk&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Case Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Err.Raise Err.Number, Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;End Select<br><br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass False<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Exit_Backup<br><br>End Sub<br><br>Function DoCompressData()<br>'Compact the Back-End Data database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSource, strDest, strCompress As String<br>'Set the path names for the original file and the temporary file to compact to<br>&nbsp;&nbsp;&nbsp;&nbsp;strSource = &quot;g:\PathStatement\Original File.mdb&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;strCompress = &quot;G:\PathStatement\TemporaryFile.mdb&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;strDest = &quot;g:\PathStatement\Original File.mdb<br><br>&nbsp;&nbsp;&nbsp;&nbsp;If MsgBox(&quot;Are you sure you want to compact the data?&quot;, vbQuestion + vbYesNo, &quot; Continue with Data Compaction?&quot = vbYes Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass True<br>&nbsp;&nbsp;&nbsp;&nbsp;'If the temp compact file exists, delete it<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Dir(strCompress) &lt;&gt; &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Kill strCompress<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;'Compact the database<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DBEngine.CompactDatabase strSource, strCompress<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;'Copy the temp file to the original filename and delete the temp file<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FileCopy strCompress, strDest<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Kill strCompress<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass False<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox (&quot;Data Compaction is Complete&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Function<br><br>Lightning<br>

 

[jimmythegeek]

If your tables are attached, see the following FAQ:<br><br><b>How can I do an automated back-up of my attached Back-End Data?</b><br><br>In the Access - General Discussion FAQ's tab. <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[chdavisjr]

Lightning,
the link you supplied back in Jul 2000 is no more (

http://www.odyssey.apana.org.au/~abrowne/homepage.html)

Do you have any idea where it might be found now?
Thank you in advance, Chalmers

 

[Lightning]

chdavisjr

Sorry, I haven't accessed that site for over a year, so I didn't even know it had shut down. I have no idea where, or even if, it is still operating.

Lightning

 

[galar]

I need to backup my back-end to the A:
If the size exeeds the 1.44mb, how do I do this?
I'm using the batch file connecting to the WinZip (using command line support) but it's not so comfortable. It must be a better way!

Thanks in advance

 

[hoggbottom59]

I am just working through this code looks good but there are mistakes. For example the On Error Goto is missing in the Sub Backup().

Will repost it when I've corrected and used it in my database.

Leon.