How to Export a Form or Report as a "Package"

[topher23]

It happens often in Access development environments: you're working in one database, and you realize that you want to use a form or report in another database that you or your company are also developing. But when you import/export the object, you discover that a lot of the objects that it depends on (tables, queries, subforms/subreports) aren't in the other database and also need import/export. Eventually, you get everything transferred, but wonder if it was really worth all the hassle.

The answer is, "No, it's not." Not when you could have done it all with VBA, that is! By accessing the object's Dependencies collection, we can find and export every object that your form or report requires to run.

The dependencies are saved in the databases system tables, not in the object definitions themselves, so they must be populated to their own object using [object].GetDependencie s. From there, it's just a matter of iterating through all of the dependencies.

The following three code procedures accomplish exporting each dependency object to a purpose-created database. You can then send the package to another developer or copy it wholesale into another working database. Paste the code into a new module to use it.

The first procedure creates the export database and passes the top-level object to the packaging procedure. The DepColl array and DepI iterator are used to hold references to all of the converted objects in order to keep from converting the same objects due to multiple dependencies. In this procedure, we also make sure that "Track Name Autocorrect Info" is set to True (1), because this creates the system table that stores all object dependencies.

'PackageObjects Module
'Example: PackageObject([Object Name],"Form" or "Report")
'2014 Topher Ritchie
'This code is free for use in development and must
'retain this section.
'=========================================================
'Module Declarations section
'=========================================================

Option Compare Database
Option Explicit

Public DepColl() As String
Public DepI As Long


Public Sub PackageObject(strObject As String, strType As String)
Dim db As Object

    Application.SetOption "Track Name AutoCorrect Info", 1
    Access.DBEngine.CreateDatabase "F:\MyDatabases\dbExport_" & Format(Date, "yyyy-mm-dd") & ".accdb", DB_LANG_GENERAL
    
    Set db = Application.CurrentProject
    If strType = "Form" Then
        OutputItem strObject, -32768
        PackageDependencies db.AllForms.Item(strObject)
    ElseIf strType = "Report" Then
        OutputItem strObject, -32764
        PackageDependencies db.AllReports.Item(strObject)
    End If
    ReDim DepColl(0)
    DepI = 0
End Sub

This next procedure is the "meat" of the module. This populates an object with the dependencies collection, then goes through each object dependency to ensure it hasn't already been exported. If it hasn't, the code exports it, then calls itself recursively using the exported object in order to get all n-level dependencies for the primary object. Unfortunately, this is where the code runs into a bit of trouble. It may run into an infinite loop in which it continues to call itself, but no new objects are being output. Fortunately, if you are watching the Immediate window, you will see when this begins to occur because no new objects will show up there. It's not the best solution, but since this code is intended for development purposes only and not for use in production systems, simply break the code at this point and stop it.

Public Sub PackageDependencies(objObject As Object)
    Dim DepObj As Object
    Dim DepObjColl As Object
    Dim i As Long
On Error GoTo Err_Exit:

    Set DepObjColl = objObject.GetDependencyInfo
    For Each DepObj In DepObjColl.Dependencies
        For i = 1 To DepI
            If DepObj.name = DepColl(i) Then GoTo SkipObj
        Next i
        DepI = DepI + 1
        ReDim Preserve DepColl(DepI)
        DepColl(DepI) = DepObj.name
        OutputItem DepObj.name, DLookup("Type", "MSysObjects", "NAME = '" & DepObj.name & "'")
        PackageDependencies DepObj
SkipObj:
    Next DepObj
    
Err_Exit:
End Sub

The final code procedure does the actual export to the holding database. Similar to the original procedure call, it gets passed an object name and type, but in this case the type, rather than being plaintext, is an integer value pulled from a system table using the DLookup statement above. This final procedure simply translates the Type into an acObjectType constant declaration for the sake of clarity.

Public Sub OutputItem(strName As String, lngType As Long)
    Debug.Print strName, lngType
    If lngType = 1 Or lngType = 6 Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", "F:\PRISM\PRISM Support Programs\Development\dbExport_" & Format(Date, "yyyy-mm-dd") & ".accdb", acTable, strName, strName
    ElseIf lngType = 5 Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", "F:\PRISM\PRISM Support Programs\Development\dbExport_" & Format(Date, "yyyy-mm-dd") & ".accdb", acQuery, strName, strName
    ElseIf lngType = -32768 Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", "F:\PRISM\PRISM Support Programs\Development\dbExport_" & Format(Date, "yyyy-mm-dd") & ".accdb", acForm, strName, strName
    ElseIf lngType = -32764 Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", "F:\MyDatabases\dbExport_" & Format(Date, "yyyy-mm-dd") & ".accdb", acReport, strName, strName
    End If
End Sub

Using this code, you will be able to output a complete Form or Report with all of its dependencies intact, ready to be used. By adding a few lines of code to the first procedure, you could also use it to transfer queries, if you'd like, but that was outside of my original scope.

[jimatqsi]

topher23,
Thanks very much, this is really helpful.

I note with dismay the need to make sure that "Track Name Autocorrect Info" is set to True (1), because I have suffered greatly at the hands of the beast on occasion. It tends to cause long, long hourglass pauses in a database with lots of objects.But now I avoid that to the extent possible.

Thanks again for a very insightful article.

Jim