Append Recordset

[scott]

I'm trying to append all records from "t_import" into table "tbl_Sales" with
sub below, but it is not doing anything, even no errors. Both tables are
inside same access db file.

Can someone help me with syntax?


Sub ImportExcelData()
Dim rs1 As ADODB.Recordset, rs2 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

rs1.ActiveConnection = CurrentProject.Connection
rs1.CursorType = adOpenStatic
rs1.LockType = adLockOptimistic
rs1.Open "Select * from t_import"

rs2.ActiveConnection = CurrentProject.Connection
rs2.CursorType = adOpenStatic
rs2.LockType = adLockOptimistic
rs2.Open "Select * from tbl_Sales"

Do Until rs1.EOF
rs1.AddNew

rs1!Size = rs2!Size
rs1!Grade = rs2!Grade
rs1!Length = rs2!Length

rs1.Update
rs1.MoveNext
Loop
rs1.close
rs2.close
Set rs1 = Nothing
Set rs2 = Nothing

End Sub

 

[Ken Snell [MVP]]

I think you've reversed some of your code's contents. Change these lines

rs1.AddNew

rs1!Size = rs2!Size
rs1!Grade = rs2!Grade
rs1!Length = rs2!Length

rs1.Update



to these lines

rs2.AddNew

rs2!Size = rs1!Size
rs2!Grade = rs1!Grade
rs2!Length = rs1!Length

rs2.Update

 

[scott]

thanks

I think you've reversed some of your code's contents. Change these lines

rs1.AddNew

rs1!Size = rs2!Size
rs1!Grade = rs2!Grade
rs1!Length = rs2!Length

rs1.Update



to these lines

rs2.AddNew

rs2!Size = rs1!Size
rs2!Grade = rs1!Grade
rs2!Length = rs1!Length

rs2.Update