Access not updating all the records

[andyc209]

I have some code that updates an access database but it does not post all the records. The Response.Write bit brings back all 9 records but the update in the same loop only writes 8 records to the database. Anyone know why?

'gets records from a SQL database
s="select * from tbl_name where code = 1"
set rs2=db.execute(s)
while not rs2.eof

'this bit writes 9 records
response.write rs2("reference")
response.write "<br>"

'this bit writes 8 records to the Access database
set rs=server.createobject("ADODB.Recordset")
s="select * from MDB_Tablename where id=0"
rs.open s,db4,3,3
rs.addnew
rs.fields("reference") = rs2("reference")
rs.update
rs.close

rs2.movenext
wend
rs2.close

 

[wvdba]

i'm not sure you can "mix" two recordsets like what you have here. you're closing rs inside the loop. rs.close should be outside of the loop.

 

[jsteph]

Could "Reference" be a unique key and are you sure you're not getting a duplicate "reference" value that might not update due to it's possible uniqueness? Or could "reference" from rs2 be blank, null, or somehow not valid for the 9th record? (assuming it's the 9th that's missing)

Also you don't need to createObject in the loop--if you .close as you're doing.

Also...do you have on error resume next which would supress any error (such as dups, invalid value, etc)?
--Jim