我是这样解决问题的
sub遍历要更新的表（t2）

Public Sub tableToUpdate()
    Dim strSQL
    Dim DataB As Database
    Dim rs As Recordset
    Dim t2_id As Integer
    Dim values As String

    Set DataB = CurrentDb()

    Set rs = DataB.OpenRecordset("Select id from T2")
    Do While Not rs.EOF

        t2_id = rs("ID")
        Parks = GetListOptimal("Select T1.id as t1_id from T1 Where T1.id_t2 = " & t2_id, ", ", "")

        strSQL = "UPDATE T2 SET T2.t1_ids = '" & values & "' WHERE T2.id = " & t2_id

        DataB.Execute strSQL, dbFailOnError
        t2_id = 0
        values = ""

        rs.MoveNext
    Loop

    rs.Close
    DataB.Close

    Set rs = Nothing
    Set DataB = Nothing
End Sub
``` `GetListOptimal` 是生成逗号分隔值的函数

' Concatenate multiple values in a query. From:
' https://stackoverflow.com/questions/5174362/microsoft-access-condense-multiple-lines-in-a-table/5174843#5174843
'
' Note that using a StringBuilder class from here:
' https://codereview.stackexchange.com/questions/67596/a-lightning-fast-stringbuilder/154792#154792
' offers no code speed up

Public Function GetListOptimal( _
SQL As String, _
Optional fieldDelim As String = ", ", _
Optional recordDelim As String = vbCrLf _
) As String

Dim dbs As Database
Dim rs As Recordset
Dim records() As Variant
Dim recordCount As Long

' return values
Dim ret As String
Dim recordString As String
ret = ""
recordString = ""

' index vars
Dim recordN As Integer
Dim fieldN As Integer
Dim currentField As Variant

' array bounds vars
Dim recordsLBField As Integer
Dim recordsUBField As Integer
Dim recordsLBRecord As Integer
Dim recordsUBRecord As Integer

' get data from db
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(SQL)

' added MoveLast to get the real number of rows
If rs.recordCount > 0 Then
rs.MoveLast
recordCount = rs.recordCount
rs.MoveFirst
End If

' Guard against no records returned
If recordCount = 0 Then
    GetListOptimal = ""
    Exit Function
End If

records = rs.GetRows(recordCount)

' assign bounds of data
recordsLBField = LBound(records, 1)    ' should always be 0, I think
recordsUBField = UBound(records, 1)
recordsLBRecord = LBound(records, 2)    ' should always be 0, I think
recordsUBRecord = UBound(records, 2)

' FYI vba will loop thorugh every For loop at least once, even if
' both LBound and UBound are 0.  We already checked to ensure that
' there is at least one record, and that also ensures that
' there is at least one record.  I think...
' Can a SQL query return >0 records with 0 fields each?

'Primo giro (per non aggiungere virgola)
Dim first As Boolean
first = True

For recordN = recordsLBRecord To recordsUBRecord
    For fieldN = recordsLBField To recordsUBField
        ' Virgola prima del record solo se non siamo al primo e ultimo giro
        If first = False Then
            recordString = recordString & fieldDelim
        Else
            first = False
        End If

        ' records is indexed (field, record) for some reason
        currentField = records(fieldN, recordN)

        ' Guard against null-valued fields
        If Not IsNull(currentField) Then
            recordString = recordString & CStr(currentField)
        End If
    Next fieldN

    ' Only add recordDelim after at least one record
    If ret <> "" Then
        ret = ret & recordDelim
    End If
    ret = ret & recordString

    recordString = ""   ' Re-initialize to ensure no old data problems
Next recordN

' adds final recordDelim at end output
' not sure when this might be a good idea
' TODO: Implement switch parameter to control
' this, rather than just disabling it
' If ret <> "" Then
'    ret = ret & recordDelim
' End If

' Cleanup db objects
Set dbs = Nothing
Set rs = Nothing

GetListOptimal = ret
Exit Function

End Function

使用的来源：
vba是否使用循环更新access中的表/记录集以及其他表/记录集的值？
access2010vba查询表并遍历结果
microsoftaccess将多行压缩到一个表中（daniel的答案有一些改进）