We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Karl001
Karl001 asked
on
Medium Priority
239 Views
Last Modified: 2013-11-27
Hi,
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?

Thanks

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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.