Access Cookbook - Ken Getz [137]
Case Else
strObjType = ""
End Select
' If this isn't one of the important containers, don't
' bother listing documents.
If strObjType <> "" Then
con.Documents.Refresh
For Each doc In con.Documents
' You can't backup the current form, since it's open.
If Not (doc.Name = Me.Name And con.Name = "Forms") Then
fReturn = SaveToCollection(strObjType,
doc.Name, _ doc.DateCreated,
doc.LastUpdated)
End If
Next doc
End If
Next con
The SaveToCollection subroutine called by FillObjArray is shown here:
Private Function SaveToCollection(ByVal strType As String, ByVal strName As String, _
ByVal strDateCreated As String, ByVal strLastUpdated As String) As Boolean
' Skip deleted objects
Dim itemInfo As Info
Set itemInfo = New Info
If Left$(strName, 1) <> "~" Then
itemInfo.ObjectType = strType
itemInfo.ObjectName = strName
itemInfo.DateCreated = strDateCreated
itemInfo.LastUpdated = strLastUpdated
mcolInfo.Add itemInfo
SaveToCollection = True
Else
SaveToCollection = False
End If
End Function
Access doesn't immediately remove database container objects that you have deleted. Instead, it renames each deleted object to a name that begins with "~TMPCLP". In addition, when you use SQL statements for row sources or record sources, Access creates hidden queries with names that also start with a tilde character ("~"). We don't want these objects to appear in the list of objects to back up, so we included code here to exclude them explicitly from the list box.
The backup process
Once you have selected one or more database objects in the lboObjects list box, you initiate the backup process by clicking on the cmdBackup command button. The event procedure attached to this button calls the MakeBackup subroutine. This routine begins by checking to see if the backup database exists. If it does, you are warned that it will be overwritten before proceeding. Next, MakeBackup creates the output database using the following code:
Set dbOutput = DBEngine.Workspaces(0). _
CreateDatabase(strOutputDatabase, dbLangGeneral)
dbOutput.Close
The output database is immediately closed, because the backup process doesn't require it to be open. MakeBackup then iterates through the selected objects and calls ExportObject, passing it the name of the output database and the name and type of the object to be backed up:
intObjCnt = 0
ctlProgress = "Backing up objects..."
For Each varItem In ctlObjects.ItemsSelected
intObjCnt = intObjCnt + 1
strType = ctlObjects.Column(0, varItem)
strName = ctlObjects.Column(1, varItem)
ctlProgress = "Backing up " & strName & "..."
DoEvents
Call ExportObject(strOutputDatabase, strType, strName)
Next varItem
The ExportObject subroutine backs up each object using the CopyObject action. ExportObject is shown here:
Private Sub ExportObject(strOutputDatabase As String, _
strType As String, strName As String)
Dim intType As Integer
Select Case strType
Case "Table"
intType = acTable
Case "Query"
intType = acQuery
Case "Form"
intType = acForm
Case "Report"
intType = acReport
Case "Macro"
intType = acMacro
Case "Module"
intType = acModule
End Select
' If export fails, let the user know.
On Error Resume Next
DoCmd.CopyObject strOutputDatabase, strName, intType, strName
If Err.Number <> 0 Then
Beep
MsgBox "Unable to backup " & strType & ": " & strName, _
vbOKOnly + vbCritical, "ExportObject"
End If
End Sub
Comments
This technique uses the CopyObject action instead of the more traditional TransferDatabase action. CopyObject, which was added in Access 2.0, provides you with the same functionality as TransferDatabase, but because it supports only Access objects it requires fewer arguments. The CopyObject action also allows you to specify a new name for the object in the destination database. This is useful if you want give the copy a name that's different from that of the source object.
Chapter 7. VBA
Most applications that are distributed to users include at least some Visual Basic for Applications (VBA) code. Because VBA