I recently needed to export all the tables from a secured mdb into a new accdb I was creating. Now, you can export each table, 1 by 1, manually, but the becomes tiresome and tedious quite quickly. Instead, I wrote the following simple function to do the work for me.

All it does is loop through the table definitions to identify each table in the database, and as it does, I export that table.

'---------------------------------------------------------------------------------------
' Procedure : ExpAllTbls
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Exports all the tables from the currentdb into the specified db.
'             Can be useful when in a secured db to quickly export all the tables
'             into a new db.  Could easily be expanded to export any other db object
'             (query, form, report, ...)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDestDb   - Fully qualified path and filename with extension of the database in which
'             to import the exported tables
'
' Usage:
' ~~~~~~
' Call ExpAllTbls("C:\Databases\dummytransfer.accdb")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2013-02-08              Initial Release
' 2         2023-09-30              Added missing variable declaration
'                                   Updated variable naming
'                                   Updated header
'---------------------------------------------------------------------------------------
Function ExpAllTbls(sDestDb As String)
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim tdfs                  As DAO.TableDefs
    Dim tdf                   As DAO.TableDef

    Set db = CurrentDb()
    Set tdfs = db.TableDefs
    For Each tdf In tdfs    'loop through all the fields of the tables
        If Left(tdf.Name, 4) = "MSys" Then GoTo Continue    'Ignore any system tables
        DoCmd.CopyObject sDestDb, tdf.Name, acTable, tdf.Name
Continue:
    Next

Error_Handler_Exit:
    On Error Resume Next
    Set tdf = Nothing
    Set tdfs = Nothing
    Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: ExpAllTbls" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

Page History

Date	Summary of Changes
2013-02-08	Initial Release
2023-09-30	Updated VBA Function