dev-resources.site

for different kinds of informations.

Import objects from another database in Access VBA

Published at

11/9/2024

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

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

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

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

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

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