Import all tables from one access database to an other access database

Posted on 2009-06-27
Medium Priority
Last Modified: 2013-11-27
I use a function to import table from one access database to an other one.
ex: import("tbl_Contact")
To import all tables from the source database, I have to submit the import function for each table.
As an example, if I have 10 tables in my access database, I would have to use 10 times the function import.
Is it possible to import all tables in only one VBA/SQL instruction?


Function import(tableName As String)
    Dim strSQLDelete As String
    Dim strSQLImport As String
    Dim directoryName As String
    directoryName = "S:\ABCentre\ABDataC.mdb"
    strSQLDelete = "DELETE *  FROM [" & tableName & "];"
    strSQLImport = "INSERT INTO " & tableName & " 
                              SELECT * From [" & directoryName & "].[" & tableName & "]"
    With DoCmd
        .SetWarnings False
            .RunSQL strSQLDelete
            .RunSQL strSQLImport
        .SetWarnings True
    End With
End Function

Open in new window

Question by:Karl001
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 24729290
you can place the tableNames in an Array or in a table

sub import()

dim tblArray(), j as integer, tableName as string

    Dim strSQLDelete As String
    Dim strSQLImport As String
    Dim directoryName As String
    directoryName = "S:\ABCentre\ABDataC.mdb"

    tblArray=Array("tbl_Contact","table1","table2", "table10")
for j=0 to ubound(tblArray)
    strSQLDelete = "DELETE *  FROM [" & tableName & "];"
    strSQLImport = "INSERT INTO " & tableName & "
                              SELECT * From [" & directoryName & "].[" & tableName & "]"
    With DoCmd
        .SetWarnings False
            .RunSQL strSQLDelete
            .RunSQL strSQLImport
        .SetWarnings True
    End With

end sub

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question