T
thebiggermac via AccessMonster.com
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
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