Re: Backup and Restore Files

Good grief, what a palaver. I can't imagine what problems you envisaged
with a split database, but whatever they might be they can't possibly have
been harder to resolve than going through this rigmarole. Having split the
database you would only need to back up the data by copying the mdb file
(which, incidentally, will often zip down to a fraction of it's size: Winzip
can easily be shelled from Access).

As for worrying about diskettes in an age when many computers ship without
diskette drives and all computers ship with CD writers...

"thebiggermac via AccessMonster.com" <u31786@uwe> wrote in message
news:72b63a7151e41@xxxxxx
A short time ago I placed on this post a simple question:

What is the best way to backup and restore a runtime database file?

I received a lot of valuable input but none that I could really use. To
understand what I was trying to accomplish let me explain the parameters
of
the program.

The program was written for yacht owners, specifically to keep track of
maintenance and fuel cost associated with operating their yacht. The
program
was designed to reside on a single computer. The license also allows it to
be
placed on a second computer, such as a laptop. The challenge became trying
to
keep the two machines in sync. A backup and restore function would
overcome
this obstacle as well as provide a restore source should a computer crash
occur.

The program is created in two flavors, one containing hull specific
information and one flavor that is completely empty. (This last flavor
would
allow someone to buy the program and place their specific data in it. The
first flavor is targeted towards yacht dealerships and hull specific
information.)

Most of the recommendations I received assumed that the program would be
placed on a server and a split database was the answer. Splitting the
database for this project was considered but rejected. Since this program
was
not designed to go on a server, the single file approach was adopted,
which
alleviated many problems associated with a split database.

Searching the posts revealed no clear cut answer to my situation.
Fortunately
there are some people in the company I work for that has far greater
knowledge on Access than I. I called upon them to assist me and over a few
days of banging our head against the wall we found the solution. I am
posting
the code we used at the end of this post, which hopefully will help
someone
else needing a backup and restore solution.

Particulars:

A form was created with two buttons: Export Data and Import Data. A module
we
called ExpImp was created and then attached to these two buttons. Since I
wanted the user to select a backup destination drive, a function had to be
implemented to call up a drive/directory window where the user could
select
the destination.

The next issue was the type of backup file to be utilized. Excel is
generally
the program of choice but I could not guarantee that everyone would have
the
same flavor of Excel on their computer. This could cause a backup and
restore
failure. Thus a text file format was decided upon. Not everyone could have
the same version of Excel, but EVERYONE has Notepad. A second benefit is
that
text files are small and one can fit a whole lot of data on a 1.44 floppy
disk (should that be the destination of choice). Writing mdb files to a
floppy would soon overwhelm the disk giving the end user one less
destination
option and me one more headache to consider.

Now a quick note to other users looking for a backup solution: text files
are
not necessarily the best option to use for backup. Consider all types of
backup solutions and select the one that best fits your situation. Text
files
create comma delimited text and you cannot control what the end user will
do
with the backup data. Text files can be edited in such a way as to make
the
restore data useless. I placed a warning in my user guide on this very
subject, it was about all I could do. Still for my situation text files
were
the answer.

One of the problems I kept running into was key violations. The database
is
related in such a way that every time I tried to restore data I got some
type
of key violation. There had to be a way around this issue. I could not
change
the relationships without drastically altering the very foundation of the
database itself. That was unacceptable. The answer came when I realized
that
if I zapped all the existing data first I could then rewrite the data and
not
receive a key violation error. Yeah it threw my autonumbering off but who
cares, no one sees that data anyway and it's not a data field I relate to.
As
you review the code you will see a function to delete all the existing
data
first before rewriting the data back to the database. Be advised this only
occurs in the Restore function of the program. Note: the way my database
is
created once I delete a hull number then all the corresponding data
related
to that hull number is also deleted, hence I only zap the hull number data
before rewriting the restore data.

The destination drive:

When the user selects Export Data, a drive/directory window pops up. Our
code
is written in such a way as it does not allow the user to create a
directory
from this window. If the user wants the data deposited in a specific
folder
then that folder must first be created via Explorer, or some other file
management program. The drive/directory window only permits you to
navigate
to the specified directory and nothing more. The same is true when you
click
the Import Data button. The same drive/directory window pops up and you
must
navigate to the proper directory to restore the data.

I am sure that there is someone out there who can write the code to create
a
directory when saving the data. Time was of essence for us so we did not
take
the initiative to create that type of code. Maybe for our next revision.

Once the drive/directory is selected and OK is clicked the data is
transferred to that location. That's all there is to it. You have backed
up
your data.

It took me a lot of frustration to find this solution and the people who
helped me deserve a great deal of credit, especially Leslie Phillips. As
mentioned, to help someone else who may be in need of this type of
solution I
am posting our module code below for you to modify and use as you see fit.


****************************************************************************
************************

Option Compare Database

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long

Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer

With bi
.hOwner = hWndAccessApp
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With

dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)

If X Then
wPos = InStr(szPath, Chr(0))
BrowseFolder = Left$(szPath, wPos - 1)
Else
BrowseFolder = vbNullString
End If
End Function


Public Sub ExportData()
Dim exp_dir As String

exp_dir = BrowseFolder("Pick a Directory")
If exp_dir <> "" Then
DoCmd.TransferText acExportDelim, , "tbl_hull_number", exp_dir & "\
tbl_hull_number.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_fuel_quantities", exp_dir &
"\
tbl_fuel_quantities.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_maintenance_log", exp_dir &
"\
tbl_maintenance_log.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_subsystems", exp_dir & "\
tbl_subsystems.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_auxilary_equipment", exp_dir
& "\
tbl_auxilary_equipment.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_boat_serial_numbers", exp_dir
&
"\tbl_boat_serial_numbers.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_EU_component_manufacturers",
exp_dir & "\tbl_EU_component_manufacturers.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_mrc_tasks", exp_dir & "\
tbl_mrc_tasks.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_mrc_tasks_revised", exp_dir &
"\
tbl_mrc_tasks_revised.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_service_company_information",
exp_dir & "\tbl_service_company_information.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_systems", exp_dir & "\
tbl_systems.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_systems_subsystems_filters",
exp_dir & "\tbl_systems_subsystems_filters.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_technician_info", exp_dir &
"\
tbl_technician_info.txt", -1
DoCmd.TransferText acExportDelim, , "tbl_US_component_manufacturers",
exp_dir & "\tbl_US_component_manufacturers.txt", -1
MsgBox ("Exported data to " & exp_dir)
Else
MsgBox ("Please try again and select a directory")
End If

End Sub
Public Sub ImportData()
Dim imp_dir As String

imp_dir = BrowseFolder("Pick a folder to import from")

If imp_dir <> "" Then
If Dir$(imp_dir & "\tbl_hull_number.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_hull_number"
DoCmd.TransferText acImportDelim, , "tbl_hull_number", imp_dir &
"\
tbl_hull_number.txt", -1
End If

If Dir$(imp_dir & "\tbl_fuel_quantities.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_fuel_quantities"
DoCmd.TransferText acImportDelim, , "tbl_fuel_quantities", imp_dir
&
"\tbl_fuel_quantities.txt", -1
End If

'holding


If Dir$(imp_dir & "\tbl_subsystems.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_subsystems"
DoCmd.TransferText acImportDelim, , "tbl_subsystems", imp_dir & "\
tbl_subsystems.txt", -1
End If

If Dir$(imp_dir & "\tbl_auxilary_equipment.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_auxilary_equipment"
DoCmd.TransferText acImportDelim, , "tbl_auxilary_equipment",
imp_dir
& "\tbl_auxilary_equipment.txt", -1
End If

If Dir$(imp_dir & "\tbl_boat_serial_numbers.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_boat_serial_numbers"
DoCmd.TransferText acImportDelim, , "tbl_boat_serial_numbers",
imp_dir & "\tbl_boat_serial_numbers.txt", -1
End If

If Dir$(imp_dir & "\tbl_EU_component_manufacturers.txt", vbNormal) <>
""
Then
DoCmd.RunSQL "Delete * from tbl_EU_component_manufacturers"
DoCmd.TransferText acImportDelim, ,
"tbl_EU_component_manufacturers",
imp_dir & "\tbl_EU_component_manufacturers.txt", -1
End If

If Dir$(imp_dir & "\tbl_mrc_tasks.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_mrc_tasks"
DoCmd.TransferText acImportDelim, , "tbl_mrc_tasks", imp_dir & "\
tbl_mrc_tasks.txt", -1
End If

If Dir$(imp_dir & "\tbl_mrc_tasks_revised.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_mrc_tasks_revised"
DoCmd.TransferText acImportDelim, , "tbl_mrc_tasks_revised",
imp_dir
& "\tbl_mrc_tasks_revised.txt", -1
End If

If Dir$(imp_dir & "\tbl_service_company_information.txt", vbNormal) <>
""
Then
DoCmd.RunSQL "Delete * from tbl_service_company_information"
DoCmd.TransferText acImportDelim, ,
"tbl_service_company_information",
imp_dir & "\tbl_service_company_information.txt", -1
End If

If Dir$(imp_dir & "\tbl_systems.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_systems"
DoCmd.TransferText acImportDelim, , "tbl_systems", imp_dir & "\
tbl_systems.txt", -1
End If

If Dir$(imp_dir & "\tbl_systems_subsystems_filters.txt", vbNormal) <>
""
Then
DoCmd.RunSQL "Delete * from tbl_systems_subsystems_filters"
DoCmd.TransferText acImportDelim, ,
"tbl_systems_subsystems_filters",
imp_dir & "\tbl_systems_subsystems_filters.txt", -1
End If

If Dir$(imp_dir & "\tbl_technician_info.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_technician_info"
DoCmd.TransferText acImportDelim, , "tbl_technician_info", imp_dir
&
"\tbl_technician_info.txt", -1
End If

If Dir$(imp_dir & "\tbl_US_component_manufacturers.txt", vbNormal) <>
""
Then
DoCmd.RunSQL "Delete * from tbl_US_component_manufacturers"
DoCmd.TransferText acImportDelim, ,
"tbl_US_component_manufacturers",
imp_dir & "\tbl_US_component_manufacturers.txt", -1
End If

If Dir$(imp_dir & "\tbl_maintenance_log.txt", vbNormal) <> "" Then
DoCmd.RunSQL "Delete * from tbl_maintenance_log"
DoCmd.TransferText acImportDelim, , "tbl_maintenance_log", imp_dir
&
"\tbl_maintenance_log.txt", -1
End If

MsgBox ("Imported data from " & imp_dir)
Else
MsgBox ("Please try again and select a directory")
End If

End Sub

--
Message posted via http://www.accessmonster.com



.