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)

D…


This content originally appeared on DEV Community and was authored by Wild Cat

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


This content originally appeared on DEV Community and was authored by Wild Cat


Print Share Comment Cite Upload Translate Updates
APA

Wild Cat | Sciencx (2024-11-09T22:27:43+00:00) Import objects from another database in Access VBA. Retrieved from https://www.scien.cx/2024/11/09/import-objects-from-another-database-in-access-vba/

MLA
" » Import objects from another database in Access VBA." Wild Cat | Sciencx - Saturday November 9, 2024, https://www.scien.cx/2024/11/09/import-objects-from-another-database-in-access-vba/
HARVARD
Wild Cat | Sciencx Saturday November 9, 2024 » Import objects from another database in Access VBA., viewed ,<https://www.scien.cx/2024/11/09/import-objects-from-another-database-in-access-vba/>
VANCOUVER
Wild Cat | Sciencx - » Import objects from another database in Access VBA. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/11/09/import-objects-from-another-database-in-access-vba/
CHICAGO
" » Import objects from another database in Access VBA." Wild Cat | Sciencx - Accessed . https://www.scien.cx/2024/11/09/import-objects-from-another-database-in-access-vba/
IEEE
" » Import objects from another database in Access VBA." Wild Cat | Sciencx [Online]. Available: https://www.scien.cx/2024/11/09/import-objects-from-another-database-in-access-vba/. [Accessed: ]
rf:citation
» Import objects from another database in Access VBA | Wild Cat | Sciencx | https://www.scien.cx/2024/11/09/import-objects-from-another-database-in-access-vba/ |

Please log in to upload a file.



There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

Related Posts