Unanswered: Inserting record appears in SELECTed recordset.
Hi, I have a problem I never thought before. This is sample model. Table A contain A_PK as primary key. It contains value 1-10. I found that the last wkRS!A_PK program can read is 101. Ofcourse, For this code, 10 is still the last record. But eventhough I change rollback to commit, result is the same, it can still read thru 101. Not more than 101. Table A now contains 1-10 and 101-110.
1) What I want is wkRS contains only records at the time of issue SQL select statement , ie. 1-10 only, What should I do ?
2) If my code does not use transaction processing, Does your method still apply ?
Here is my code :
Dim wkCN As New ADODB.Connection
Dim wkRS As New ADODB.Recordset
wkCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb"
wkRS.Open "select * from A order by A_PK ", wkCN, adOpenKeyset, adLockOptimistic
Do While Not wkRS.EOF
wkCN.Execute "insert into A (A_PK) values (" & CStr(wkRS!a_pk + 100) & ")"
wkRS.Close: Set wkRS = Nothing
wkCN.Close: Set wkCN = Nothing