Backup of Back-end via code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a split 2003 Access program. The code is Wc.mdb and the data is
WCData.mdb. I want to have a button to press to get it to back up the linked
data (WCData - NOT the program portion) only. I don't want them to have to go
to the backend to do it either. I'd like to be able to do it from the
front-end - if possible. Eventually, I would also like to have a option to
restore from an older version of the data. Any one do this before? How
would one code this? I'm sure the backup is intiated through the "docmd",
but that all. Can you help?

Thanks so much, in advance!!
 
hi,
It might be dangerous to try to back up a db while someone
is in it. that is why you don't see a save menu item in
access. and why access automaticly save records in a table.
To create a back up to be restored later would involed
copying tables. i have done this in several of my apps.
(not to a different table for backup, just for the restore
function)
i would create a macro to copy all the tables to another
db and date them. and do it on a quiet system.
another way would be to use the drag and drop method from
windows explorer. I have done this with small dbs to my
flash drive so that i can take my work home with me.
mechanical yes but it works.
good luck
 
Hi
I have just completed some code which does what you suggest from a button on
the front end. What I did was to make a copy of the front end and delete all
the table links from it. This copy should reside in the same directory as
the FE. Then take another copy of the copy and rename it to whatever you
like, maybe including a date if you wish.This can be automated in the
following way:

Source = 'path to your copied FE

'change dividers in date as it will be used in filename and '/ ' is
illegal
datestamp = Left(Now(), 2) & "_" & Mid(Now(), 4, 2) & "_" & Mid(Now(),
7, 2)

Destination = "C:/YourFolder/Copy of FE database " & datestamp & ".mde"

'if the YourFolder folder doesn't exist on this computer, create it.
If Len(Dir("C:\YourFolder", vbDirectory)) = 0 Then

MkDir "C:\YourFolder"

End If

'make a copy of the Take Home database with new name
FileCopy Source, Destination

The first copy remains in its original state until the next time you need it.
The following code then imports the tables to the FE, and then exports them
to the copied FE, where dbPathFrom and dbPathTo are variables containing the
paths to the BE and copied FE respectively. It is necessary to do it this
way because the TransferDatabase method doesn't support moving objects
between two remote databases.

'loop through the table defs
For Each oTBL In CurrentDb.TableDefs

'pass the next table name to the variable
sTableName = oTBL.Name

'import tables to the FE,
DoCmd.TransferDatabase acImport, "Microsoft Access", _
DbPathFrom, acTable, sTableName, sTableName

'export tables to the copied FE,
DoCmd.TransferDatabase acExport, "Microsoft Access", _
DbPathTo, acTable, sTableName & "1", sTableName

Next

The next code deletes the copied tables from the FE:

'Return the Main database to its previous state
'by deleting the tables. To refer to the actual names
'of the tables, append "1" because they have been
'named in this way when they were imported by Access
'to avoid duplication of the link names
'loop through the table defs
For Each oTBL In CurrentDb.TableDefs

'pass the next table name to the variable
sTableName = oTBL.Name

'delete the table
sTableName = oTBL.Name & "1"
Access.DoCmd.DeleteObject acTable, sTableName

Next

Finally, I found that Access created some temporary system tables while it
was doing the previous operations, so this code deletes them. I am not sure
whether these tables will be the same for you so some trial and error may be
called for here.

'delete the system tables

sTableName = "MSysACEs1"
Access.DoCmd.DeleteObject acTable, sTableName
sTableName = "MSysObjects1"
Access.DoCmd.DeleteObject acTable, sTableName

sTableName = "MSysQueries1"
Access.DoCmd.DeleteObject acTable, sTableName

sTableName = "MSysRelationships1"
Access.DoCmd.DeleteObject acTable, sTableName

The result should be a database file which is completely self contained and
can be used as a stand alone backup or archive which can be refered to
without the need to connect to the FE or BE. The method is a little
convoluted but it works fine, and doesn't seem to worry whether anyone else
is using the database at the time. If you are concerned about that I can show
you how to test for other users...

If all this makes any sense to you please feel free to use the code.
 
The trouble with doing this while the file is open and in use (i.e. not when
all users are out and you have exclusive access) is that your code backs up
Table1, and then moves on to Table2 etc.

Because of the time difference, it is possible that another user has modifed
Table6 before you get there, and that change has caused a change to Table1
(e.g. cascading delete). As a result, your copy of Table1 make a few minutes
earlier is out of sync with the copy of Table6, and the data is
conconsistent.
 
Good point Allen
My database only has two or three users, who are sitting next to each other,
so the situation is easy to control. However, as I said at the end of my
post, you can always test for other users and not allow the operation if
necessary. I will probably include this refinement when time allows...

Regards
 
Personally, I always rename the existing backend (using the Name statement),
and then compact the renamed backend to its "proper" name using the
CompactDatabase method of the DBEngine object.
 
Back
Top