import data from multiple .mdb tables into one .mdb table

[coolbear91]

dear all,



how can I import from multiple tables into one table without replacing the destination table.
so that the destination table consist of old data and new imported data.
for example:
I have table A with date 130205 and I need to import from table B and C which the dates are 130206 and 130207.
those 3 tables already have the same column numbers and data type.

kindly need your advice regarding to this.
thank's.

[Rod]

Read up on APPEND queries. If after that you still need specific help, post back.

[orange]

In addition to Rod's comment, I recommend you research Normalization.
Good luck with your project.

[coolbear91]

thank's for your clue guys.
but I still need some help.

to resolve my needs, I already have this code snippet as below:

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 appends new rows from other database.
then what should I do if I need to do some changes in that code so that I can import, not only from SGDPS1A_20130301, but also from SGDPS1A_20130302, SGDPS1A_20130303, SGDPS1A_20130304 and so on (depend on how many source databases within that PATH)?

please advice.

[orange]

Why is there a database for each day? Perhaps you should tell us the business facts and why and how the business operates; then how the database(s) support the business.

[Rod]

No answer to Orange yet?

Anyway your question has theoretical merit so let me hint at two possible solutions.

To incorporate everything into a single SQL query means nested append queries. I've never seen this done but it should be possible. However the restriction is that there must be the same number of sources each time and the names of these sources must be constant. Anyone ever written nested append queries?

The second method that gives much more flexibility is to write a VBA routine that 'drives' the SQL query. You can test for existence of the target and empty it if necessary; test for each source occurrence and append only if it exists; etc.; etc.

[Rod]

I tried nesting append queries - it did not work. So I think you're stuck with the VBA solution.

[June7]

Can append records from a UNION query. Example:

INSERT INTO Table3 SELECT * FROM (SELECT [Date], EmpID, StartTime AS TimeRec, Area, "T1" AS Source FROM Table1
UNION ALL SELECT [Date], EmpID, [In], Area, "T2" AS Source FROM Table2);

However, I have encountered issue with VBA executing SQL statements that involve UNION. I had to redesign a procedure to not rely on UNION.

[coolbear91]

dear all,

I'm working in some telecommunication company.
one of my routine task is to fetch data from nodes.
we have 17 nodes and each nodes have log files (xml format).
those files then parsed into .mdb files as the result.
so, here we have 17 .mdb files per day.

I need to accumulate all those files into one new database, which will then be updated daily.
and I have definitely found VBA code that I need to solve this issue as below:

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

this code is really works as my needs.
so, many thanks to you guys for your advice and share here.

[Rod]

Great news, well done. Yes, that's typical of the VBA code I had in mind. Make sure you empty the G:\PATH directory each day otherwise you will be appending yesterday's files as well!