loop and add from query to table

[scottdg]

I have a table that on button click needs to have all the records removed and then new records added from a query. The table is a list of store_name, store_id, employee_name and address, and amount_owed. I want the code to loop through and check each time to make sure that a record does not already exist. If it does rather than write to the fields listed above the name and address should be written to fields called employee_name2 and address2. I can delete the data from the table with no problem. But I am not sure how to add. In my code below I tried just writing the store_ID (primary key) to the table and I do not receive an error but nothing is written. I didn't want to add any additional logic until I make sure I am writing it to the table correctly.
Dim rs As adodb.Recordset
Dim rs2 As adodb.Recordset
Dim tbl As String
Dim qry As String


tbl = "Select * from myTable"
qry = "Select * from myQuery"
Set rs = New adodb.Recordset
Set rs2 = New adodb.Recordset
'Delete records from myTable
With rs
.Source = tbl
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open

Do Until .EOF
.Delete
.MoveNext
Loop
End With
'End Delete

With rs
Do Until rs.EOF
Debug.Print rs.Fields("Store_ID")
rs.AddNew
rs.Fields(3) = rs2!AgentNo
rs.Update
rs.MoveNext
Loop
End With

[pbaldy]

Wow. Okay, rs is already at EOF when you try to add, so I doubt it would add anything anyway. rs2 is never set to anything, so I don't see how it could have a value. I'd empty with simply:

CurrentDb.Execute "DELETE * FROM TableName"

You can add records with an append query, you don't need to loop. If you did need to, you'd loop the query and add to the table.

[ranman256]

you dont loop thru records to delete them,
instead you make a delete query and do it all at once.
Add criteria if needed.

[scottdg]

Yea - I know. This was being converted from an old VB6 application I was having trouble getting to work. I realized the delete and forgot to change it before I posted.