Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    5

    Question Unanswered: VBA to import table from multiple databases

    dear all,
    I'm newly using macro in Access 2010 and I have code snippet as below:

    Code:
    Dim SQL As String
    
    SQL = "INSERT INTO attCombiAttach_ROP_RAW "
    SQL = SQL & "SELECT * "
    SQL = SQL & " FROM attCombiAttach_ROP_RAW" & " " & "IN 'G:\PATH\SGDPS1A_20130301.mdb'; "
    
    CurrentDb.Execute SQL
    it successfully runs append query between 2 tables (same name, attCombiAttach_ROP_RAW) and 2 databases.

    my question is, could you guys do some change in that code so that it can import table from more than one database at the same time?
    (depend on how many databases within that PATH)
    for example, there are:
    G:\PATH\SGDPS1A_20130302.mdb
    G:\PATH\SGDPS1A_20130303.mdb
    G:\PATH\SGDPS1A_20130304.mdb


    what command should I add?
    to read the number of .mdb files then use this numbers as a loop in string query.
    Code:
    SQL = SQL & " FROM attCombiAttach_ROP_RAW" & " " & "IN 'G:\PATH\SGDPS1A_[loopingdate].mdb'; "
    your advice will be highly appreciate.
    thank's.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use something like:
    Code:
    Sub ImportDatabases()
    
        Const c_Path As String = "G:\PATH\"
        Const c_SQL As String = "INSERT INTO attCombiAttach_ROP_RAW " & _
                                "SELECT * FROM attCombiAttach_ROP_RAW IN '@N';"
        
        Dim strDbName As String
        Dim strSQL As String
        
        strDbName = Dir(c_Path & "*.mdb")
        Do Until Len(strDbName) = 0
            strDbName = c_Path & strDbName
            strSQL = Replace(c_SQL, "@N", strDbName)
            CurrentDb.Execute strSQL, dbFailOnError
            strDbName = Dir
        Loop
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    You could use something like:
    Code:
    Sub ImportDatabases()
    
        Const c_Path As String = "G:\PATH\"
        Const c_SQL As String = "INSERT INTO attCombiAttach_ROP_RAW " & _
                                "SELECT * FROM attCombiAttach_ROP_RAW IN '@N';"
        
        Dim strDbName As String
        Dim strSQL As String
        
        strDbName = Dir(c_Path & "*.mdb")
        Do Until Len(strDbName) = 0
            strDbName = c_Path & strDbName
            strSQL = Replace(c_SQL, "@N", strDbName)
            CurrentDb.Execute strSQL, dbFailOnError
            strDbName = Dir
        Loop
        
    End Sub

    many thank's pal!!
    it really works as my needs.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •