vba access append multiple tables into one

问题:

First off I'm a novice at programming.

Question I have built the code below from many examples off the internet. The database is named "Code Holder" at this time I have a table "test" and into that table I want append as many tables as there are in the database.

1. All columns will be the same for all tables
2. The table names other than "Test" will change

What I have so far is below, The code runs fine, but I can't seem to get each table to append into the "Test" table, each table comes up blank in the SQL string

Sub append4()
    Dim db As Database
    Dim tdf As TableDef
    Dim rs As Recordset         
    Set db = currentdb()
    Set rs = db.OpenRecordset("test")

    For Each tdf In db.TableDefs
                StrSQL = "INSERT INTO " & "test" & " " & _
                "SELECT * " & _
                "FROM " & "rs!tablename" & " ;"
            DoCmd.RunSQL StrSQL
        Next tdf

    Set db = Nothing

End Sub

I want to say that I haven't set rs. correctly but I'm not certain. Any help would be appreciated.

Thanks

回答1:

Afternoon, after posting I came across somthing that really helped. Below is the updated VBA code and after testing it works for me.

Thanks Barett, yes I was referencing a table incorrectly, but that's what happens when you stare at somthing for way too long.

Feel free to copy and use if you'd like

'please note there are a few things that one assumes while using this code
'1 all tables column headers are the same
'2 this was used with Access 2010


Sub testeroony2()


Dim db As DAO.Database
Dim tdf As DAO.TableDef
'you can set this database to other databases if you wanted too
Set db = currentdb
For Each tdf In db.TableDefs
    ' ignore system and temporary tables
    'if you want to use it for your own use then you will need to change "test" that is the main table that gets uploaded too
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "test") Then
                'you will need to also change "test" below to the main table you want appended too
                StrSQL = "INSERT INTO " & "test" & " " & _
                "SELECT * " & _
                "FROM " & tdf.Name & " ;"
            DoCmd.RunSQL StrSQL
    End If
Next
Set tdf = Nothing
Set db = Nothing
End Sub