Revisited this issue. Modified code slightly. This worked:
Code:
Public Sub copyFromMV()
Dim rsOld As DAO.Recordset
Dim rsMVold As DAO.Recordset
Dim rsMVnew As DAO.Recordset
Dim rsNew As DAO.Recordset
Set rsOld = CurrentDb.OpenRecordset("SELECT * FROM Table1;")
Set rsNew = CurrentDb.OpenRecordset("Table2")
Do While Not rsOld.EOF
rsNew.AddNew
rsNew!ID = rsOld!ID
'the value property returns another recordset
Set rsMVold = rsOld.Fields("Test").Value
Set rsMVnew = rsNew.Fields("Test").Value
Do While Not rsMVold.EOF
rsMVnew.AddNew
rsMVnew.Fields(0) = rsMVold.Fields(0)
rsMVnew.Update
rsMVold.MoveNext
Loop
rsNew.Update
rsOld.MoveNext
Loop
End Sub