Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    28

    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"
    wkCN.BeginTrans
    wkRS.Open "select * from A order by A_PK ", wkCN, adOpenKeyset, adLockOptimistic
    Do While Not wkRS.EOF
    Debug.Print wkRS!A_PK
    wkCN.Execute "insert into A (A_PK) values (" & CStr(wkRS!a_pk + 100) & ")"
    wkRS.MoveNext
    Loop
    wkCN.RollbackTrans
    wkRS.Close: Set wkRS = Nothing
    wkCN.Close: Set wkCN = Nothing

    Thanks in advance

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Make your recordset forwardonly. Since your recordset is currently set to keyset then additions are reflected in the recordset ....

  3. #3
    Join Date
    Nov 2003
    Posts
    28
    Hi, Thanks for your reply.

    But that prevent me from insert record I need also. How to solve ???

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    But that prevent me from insert record I need also. How to solve ???
    Actually it doesn't. You perform your insertion on the connection object. If you were attempt to do AddNew on the recordset then you'd have that problem.

    Something I just noticed is that you do not perform a CommitTrans ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •