MS Access – VBA – Export Database Objects to Another Database

I while back, I wanted to unsecure a database.  Instead of messing around with accounts….  I simply decided to export everything, all the database objects: tables, queries, forms, reports, macros, modules into a new unsecured database.  Now you can right-click on each object, one at a time, select export, browse to find the database, click ok, ok…   but this is simply a complete waste of time.

Don’t ask me why you can’t, using multiple selected objects, perform an export?!  this to me is the type of oversight made by MS’ development team, but this is another discussion altogether.

The good news is that we can easily accomplish a complete export using the power of VBA and a few very simple lines of code!

'---------------------------------------------------------------------------------------
' Procedure : ExpObj2ExtDb
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Export all the database object to another database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sExtDb    : Fully qualified path and filename of the database to export the objects
'             to.
'
' Usage:
' ~~~~~~
' ExpObj2ExtDb "c:\databases\dbtest.accdb"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-Sep-27                 Initial Release
'---------------------------------------------------------------------------------------
Public Sub ExpObj2ExtDb(sExtDb As String)
    On Error GoTo Error_Handler
    Dim qdf             As QueryDef
    Dim tdf             As TableDef
    Dim obj             As AccessObject

    ' Forms.
    For Each obj In CurrentProject.AllForms
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acForm, obj.Name, obj.Name, False
    Next obj

    ' Macros.
    For Each obj In CurrentProject.AllMacros
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acMacro, obj.Name, obj.Name, False
    Next obj

    ' Modules.
    For Each obj In CurrentProject.AllModules
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acModule, obj.Name, obj.Name, False
    Next obj

    ' Queries.
    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Name, 1) <> "~" Then    'Ignore/Skip system generated queries
            DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                                   acQuery, qdf.Name, qdf.Name, False
        End If
    Next qdf

    ' Reports.
    For Each obj In CurrentProject.AllReports
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acReport, obj.Name, obj.Name, False
    Next obj

    ' Tables.
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then    'Ignore/Skip system tables
            DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                                   acTable, tdf.Name, tdf.Name, False
        End If
    Next tdf

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set tdf = Nothing
    Set obj = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ExpObj2ExtDb" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Voilà, nothing to it (once you do it once).

21 responses on “MS Access – VBA – Export Database Objects to Another Database”

  1. George P.

    Just wanted to thank you. This has saved me soooo much time! Thank you for sharing with those of us who do not code as well as you in VBA.

  2. Grace M

    This is so great!! I’m so glad there are talented coders such as yourself out there to help where MS development team drops the ball. You have saved me so much time and aggravation. I appreciate you sharing.

    1. Daniel Pineault Post author

      The From is the current database, so nothing to specify.
      The To is specified when you supply the sExtDb input variable.

      You can run it from the immediate window by running the command as shown in the Usage example: ExpObj2ExtDb “c:databasesdbtest.accdb”

      1. J

        When I try to run this i can export all objects except queries and tables. It gives and “error 91”

        1. Daniel Pineault Post author

          I don’t know what to tell you as I just tested 2 times and it worked just fine even if I omitted Tables and Queries. I’m assuming you have a typo or some other issue, but can’t say for sure since I don’t see you code. You’re best to post a question in a forum to troubleshoot it.

  9. Johnathan Manma

    Hi there,

    As a person, who never touches the VBA code in MS Access, how would I go about applying this code to my database? I am trying to export my entire database to another database – how would I go about applying this VBA code?

    1. Daniel Pineault Post author

      Why not simply use the Access command from the Import & Link group in the External Data tab? Open the other db and run the command to import everything in one shot.

  10. Robert Martellini

    Thank you for sharing this Daniel. I was going to use thus for an upgrade to an existing db I created a while ago.
    It just occurred to me that If I export a table with the same name, but the new table has 4 extra fields, I hope it would not affect the clients data? I think Access just replaces the table and there will be no data? I only wanted to change the structure and not affect the data.
    Anyways, I am very grateful that you seem to have solutions when I needed them. In the past I used your frm Connect solution to connect fe and be constant..

  12. Andrew

    Does this work with .adp files? I am getting the Error 91 mentioned by j above for the tables and queries that use currentdb when trying to export from .adp to .accdb.

  13. kerri anguiano

    How Would I change the code so I can pick and choose what tables or querys I want to export. I only want to export part of my database, but I still want to automate it. Its very specific on what data in my table I need to export. I am only exporting some of my querys and some of my tables.

    1. Daniel Pineault Post author

      I’d probably setup a form with list boxes to make a selection of the object and then iterate over the processed items.

      If you want it all done through code, then you’d need to build an array of the objects and loop through them to perform the export.

  14. Steve Yun

    Thank you very much for sharing the code.
    I wanted to have something just like this for reducing file size of my database. When I worked on the database for a while the size get bigger and bigger rapidly some times. Then exporting all objects to a new database to get much smaller file size. I don’t know why but it worked. To do that I exported many tables and queries one by one. Now my fingers can take more rest. Thank you again.