Solved

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

Posted on 2009-06-27
1
174 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

0
Comment
Question by:Karl001
1 Comment
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 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)
    tableName=tblArray(j)
    strSQLDelete = "DELETE *  FROM [" & tableName & "];"
    strSQLImport = "INSERT INTO " & tableName & "
                              SELECT * From [" & directoryName & "].[" & tableName & "]"
   
    With DoCmd
        .SetWarnings False
            .RunSQL strSQLDelete
            .RunSQL strSQLImport
        .SetWarnings True
    End With
next

end sub
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

708 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now