DEV Community

Wild Cat
Wild Cat

Posted on

Import objects from another database in Access VBA

Introduction

This article covers the ways for importing Tables, Queries, Modules, Forms and Reports in Access VBA.

Import Tables, Queries, Modules, Forms and Reports

Public Sub ImportAllObjects(ByVal filePath As String)

    Dim currentTable As TableDef
    Dim currentQuery As QueryDef
    Dim dc As Document
    Dim dbs As DAO.Database

    Set dbs = OpenDatabase(filePath)

    'Import Tables except System Tables
    For Each currentTable In dbs.TableDefs
        If Left(currentTable.Name, 4) <> "MSys" Then
            DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acTable, currentTable.Name, currentTable.Name, StructureOnly:=False
        End If
    Next

    'Import Queries
    For Each currentQuery In dbs.QueryDefs
        DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acQuery, currentQuery.Name, currentQuery.Name
    Next

    'Import Modules
    For Each dc In dbs.Containers("Modules").Documents
        DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acModule, dc.Name, dc.Name
    Next

    'Import Forms
    For Each dc In dbs.Containers("Forms").Documents
        DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acForm, dc.Name, dc.Name
    Next

    'Import Reports
    For Each dc In dbs.Containers("Reports").Documents
        DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acReport, dc.Name, dc.Name
    Next

    dbs.Close: Set dbs = Nothing

    RefreshDatabaseWindow

End Sub
Enter fullscreen mode Exit fullscreen mode

Import Tables

Public Sub ImportTables(ByVal filePath As String)

    Dim currentTable As TableDef
    Dim dbs As DAO.Database

    Set dbs = OpenDatabase(filePath)

    'Import Tables except System Tables
    For Each currentTable In dbs.TableDefs
        If Left(currentTable.Name, 4) <> "MSys" Then
            DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acTable, currentTable.Name, currentTable.Name, StructureOnly:=False
        End If
    Next

    dbs.Close: Set dbs = Nothing

    RefreshDatabaseWindow

End Sub
Enter fullscreen mode Exit fullscreen mode

Import Queries

Public Sub ImportQueries(ByVal filePath As String)

    Dim currentQuery As QueryDef
    Dim dbs As DAO.Database

    Set dbs = OpenDatabase(filePath)

    For Each currentQuery In dbs.QueryDefs
        DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acQuery, currentQuery.Name, currentQuery.Name
    Next

    dbs.Close: Set dbs = Nothing

    RefreshDatabaseWindow

End Sub
Enter fullscreen mode Exit fullscreen mode

Import Modules

Public Sub ImportModules(ByVal filePath As String)

    Dim dc As Document
    Dim dbs As DAO.Database

    Set dbs = OpenDatabase(filePath)

    For Each dc In dbs.Containers("Modules").Documents
        DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acModule, dc.Name, dc.Name
    Next

    dbs.Close: Set dbs = Nothing

    RefreshDatabaseWindow

End Sub
Enter fullscreen mode Exit fullscreen mode

Import Forms

Public Sub ImportForms(ByVal filePath As String)

    Dim dc As Document
    Dim dbs As DAO.Database

    Set dbs = OpenDatabase(filePath)

    For Each dc In dbs.Containers("Forms").Documents
        DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acForm, dc.Name, dc.Name
    Next

    dbs.Close: Set dbs = Nothing

    RefreshDatabaseWindow

End Sub
Enter fullscreen mode Exit fullscreen mode

Import Reports

Public Sub ImportReports(ByVal filePath As String)

    Dim dc As Document
    Dim dbs As DAO.Database

    Set dbs = OpenDatabase(filePath)

    For Each dc In dbs.Containers("Reports").Documents
        DoCmd.TransferDatabase acImport, "Microsoft Access", filePath, acReport, dc.Name, dc.Name
    Next

    dbs.Close: Set dbs = Nothing

    RefreshDatabaseWindow

End Sub
Enter fullscreen mode Exit fullscreen mode

Top comments (0)