VBA访问将多个表追加到一个 [英] vba access append multiple tables into one

问题描述

首先，我是编程新手.

问题我已经从互联网上的许多示例中构建了以下代码. 这个时候数据库被命名为代码持有人"，我有一个表"test"，并且我想要在表中追加与数据库中一样多的表.

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. 所有表的所有列均相同
2. 测试"以外的表名将更改

我到目前为止所拥有的是， 代码运行正常，但我似乎无法将每个表都添加到测试"表中，每个表在SQL字符串中都为空白

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

我想说我还没有设置rs.正确，但我不确定. 任何帮助将不胜感激.

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

谢谢

推荐答案

下午，发布帖子后，我发现了一些确实有用的东西.下面是更新的VBA代码，经过测试，它对我有用.

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

感谢Barett，是的，我没有正确地引用表格，但这就是当您凝视某物太长时间时会发生的情况.

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

这篇关于VBA访问将多个表追加到一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！