?
Solved

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

Posted on 2009-06-27
1
Medium Priority
?
223 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 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)
    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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

589 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