Export all data from a Microsoft Access database

Applies to: Access 2024, Access 2021

This topic explains how an IT administrator can export all data and objects from a Microsoft Access database to text files, enabling use with other tools and supporting data portability.

For data stored in Tables, data can be exported to a variety of other formats using Access Wizards, such as Excel, see Export data to Excel, or text, see Export data to a text file.

The following instructions demonstrate how to export objects using VBA code, using the DoCmd.TransferText method command to export table data, and the Application.SaveAsText command for other objects.

The code provided will create a function that can be called with the full path to a database and a destination folder. It will then create a text file for each object in the database, which will contain the data from tables, and the definitions of all user created objects such as forms or reports.

Since this process uses OLE automation, the code could also be written using PowerShell or using a .NET language such as C#. For this example, we use VBA.

1. Create a new database.
2. Create a new module.
3. Copy and paste the following export database code into the module.
4. For each database that you wish to export, enter the following command in the immediate window:
   
   ExportAccessDatabase "_Full path to database_", "_Folder to contain exported objects_"
   
   1. Note that a new folder will be created in the target folder with the name of the exported database. This is to allow exporting multiple databases to the same target folder while keeping the contents of each exported database in a separate folder.
   2. If the database to be exported contains a startup form, or an AutoExec macro, startup actions may interfere with the export process, so you should reset the Startup Form property for the database, and remove or rename the AutoExec macro before attempting to call ExportAccessDatabase.
5. If you wish to further automate this process, you could write a function that, given the path to a folder, enumerates all the databases in the folder, and calls the ExportAccessDatabase function to export each Microsoft Access database to a target folder.

Export database code (VBA)

Option Compare Database
Option Explicit

' Pass in the location of the database to export, and the folder to export to
Sub ExportAccessDatabase(strDBPath As String, exportFolder As String)
    Dim objAccess As Object
    Dim db As Database
    Dim td As TableDef
    Dim qd As QueryDef
    Dim doc As Document
    Dim cont As Container

    On Error GoTo Err_ExportDatabase
    
    ' Create a new instance of Access
    Set objAccess = CreateObject("Access.Application")

    ' Open the database
    objAccess.OpenCurrentDatabase strDBPath
    
    ' Create a new folder using the name of the database to hold all the exported objects if it does not already exist
    exportFolder = exportFolder & Mid(strDBPath, InStrRev(strDBPath, "\"), InStr(strDBPath, ".") - InStrRev(strDBPath, "\")) & "\"
    
    If Dir(exportFolder, vbDirectory) = "" Then
        MkDir exportFolder
    End If

    Set db = objAccess.CurrentDb()

    ' Export all objects to export location with a name based on the type and name of the object

    ' Export Tables
    For Each td In db.TableDefs
        If Left(td.Name, 4) <> "MSys" Then ' Skip Microsoft Access system tables
            objAccess.DoCmd.TransferText acExportDelim, , td.Name, exportFolder & "Table_" & td.Name & ".txt", True
        End If
    Next td

    ' Export Forms
    For Each doc In db.Containers("Forms").Documents
        objAccess.SaveAsText acForm, doc.Name, exportFolder & "Form_" & doc.Name & ".txt"
    Next doc

    ' Export Reports
    For Each doc In db.Containers("Reports").Documents
        objAccess.SaveAsText acReport, doc.Name, exportFolder & "Report_" & doc.Name & ".txt"
    Next doc

    ' Export Macros
    For Each doc In db.Containers("Scripts").Documents
        objAccess.SaveAsText acMacro, doc.Name, exportFolder & "Macro_" & doc.Name & ".txt"
    Next doc

    ' Export Modules
    For Each doc In db.Containers("Modules").Documents
        objAccess.SaveAsText acModule, doc.Name, exportFolder & "Module_" & doc.Name & ".txt"
    Next doc

    ' Export Queries
    For Each qd In db.QueryDefs
        ' Skip Microsoft Access temporary queries
        If Left(qd.Name, 3) <> "~sq" Then
            objAccess.SaveAsText acQuery, qd.Name, exportFolder & "Query_" & qd.Name & ".txt"
        End If
    Next

    objAccess.Quit

    Set db = Nothing
    Set cont = Nothing
    Set objAccess = Nothing

    MsgBox "Export complete to " & exportFolder, vbInformation
    
GoTo Exit_Sub

Err_ExportDatabase:
    MsgBox Err.Number & ": " & Err.Description
 
Exit_Sub:
End Sub