DEV Community

Wild Cat
Wild Cat

Posted on

Import objects from another database in Access VBA

#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)