Solved

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

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

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

813 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

18 Experts available now in Live!

Get 1:1 Help Now