Solved

ADO Bug?

Posted on 2002-05-04
6
238 Views
Last Modified: 2013-11-23
I have an ado recordset populated with records.  I am stepping through the recordset backwards and deleting each record.

The first time I attempt to decrement the loop (after deleting my first record) it skips a record and I end up at the third record.  This never happens again throughout the process.  The result is every record gets deleted except for the second record.

        sql = "Select * from tblmeetings where recurringmeetingid = " & mRecurringMeetingID
       
        Set MeetingRS = New Recordset
       
        MeetingRS.Open sql, DBConnect, adOpenDynamic, adLockOptimistic
        If Not MeetingRS.EOF Then
            MeetingRS.MoveLast
            Do While Not MeetingRS.BOF
                MeetingRS.MovePrevious
            Loop
        End If
0
Comment
Question by:Quatro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 1

Author Comment

by:Quatro
ID: 6990211
I made a mistake in the algorithm I posted.  It should look like this:


       sql = "Select * from tblmeetings where recurringmeetingid = " & mRecurringMeetingID
       
       Set MeetingRS = New Recordset
       
       MeetingRS.Open sql, DBConnect, adOpenDynamic, adLockOptimistic
       If Not MeetingRS.EOF Then
           MeetingRS.MoveLast
           Do While Not MeetingRS.BOF
               MeetingRS.Delete 'This line was forgotten in the last post
               MeetingRS.MovePrevious
           Loop
       End If
0
 
LVL 33

Expert Comment

by:hongjun
ID: 6990218
How about this
Do While Not MeetingRS.BOF AND Not MeetingRS.EOF


hongjun
0
 
LVL 1

Author Comment

by:Quatro
ID: 6990222
I tried something similar to that.  The problem is that even though it skips a record, it still finishes at the BOF record.  As a result it would then exit the loop.

Also the code I posted is slightly simplified in comparison to the real algorithm.  The real one choosed not to delete certain records so I can't even continually loop until every record is deleted.

I have used this work around for now but its dodgy as hell and I really don't want to do it this way:

This is the real function in total:

Public Function RemoveAssociatedMeetings(Optional RemoveOnlyNonUserAlteredRecs As Boolean = True)
    Dim MeetingRS As Recordset
    Dim sql As String
    Dim TestPass As Long
    'Remove instances of the meeting from all meetings created by the recurrence
    'Deleting those that were not altered by the user
    If mRecurringMeetingID > 0 Then
        TestPass = 1
       
        'This loop is necessary due to a bug in ado that causes the second record to be skipped
        'This will run through the recordset as many times as it needs to until all records
        'that need to be deleted are removed.
        Do While TestPass <> 0
            sql = "Select * from tblmeetings where recurringmeetingid = " & mRecurringMeetingID
           
            Set MeetingRS = New Recordset
           
            MeetingRS.Open sql, DBConnect, adOpenDynamic, adLockOptimistic
           
            TestPass = 0
            If Not MeetingRS.EOF Then
                MeetingRS.MoveLast
                Do While Not MeetingRS.BOF
                    If (MeetingRS!UserAltered = True And RemoveOnlyNonUserAlteredRecs = True) Or CLng(MeetingRS!RDate) < CLng(VBA.Date) Then
                        MeetingRS!RecurringMeetingID = 0
                    Else
                        TestPass = 1
                        MeetingRS.Delete
                    End If
                   
                    MeetingRS.MovePrevious
                Loop
            End If
            MeetingRS.Close
            Set MeetingRS = Nothing
        Loop
    End If
End Function
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 12

Accepted Solution

by:
roverm earned 200 total points
ID: 6990467
First of all:
If you want to delete all records, why don't you use the DELETE SQL statement:

"DELETE FROM tblmeetings where recurringmeetingid = " & mRecurringMeetingID

Second:
Use an UPDATE (or UPDATEBATCH if the recordset is disconnected) af each deletion:

Do While Not MeetingRS.BOF
  MeetingRS.Delete
  MeetingRS.Update
  MeetingRS.MovePrevious
Loop

Last:
Why loop backwards ? If you always delete the first record the EOF will automaticly be set:

MeetingRS.MoveFirst
Do While Not MeetingRS.EOF
  MeetingRS.Delete adAffectCurrent
Loop

D'Mzzl!
RoverM
0
 
LVL 1

Author Comment

by:Quatro
ID: 6990666
> "DELETE FROM tblmeetings where recurringmeetingid = " & mRecurringMeetingID

The sql will be a little more complex than that but I think I can use it.


>Second:
>Use an UPDATE (or UPDATEBATCH if the recordset is disconnected) af each deletion:

>Do While Not MeetingRS.BOF
> MeetingRS.Delete
> MeetingRS.Update
> MeetingRS.MovePrevious
>Loop

I tried that and it made no difference.

>Why loop backwards ? If you always delete the first record the EOF will automaticly be set:

My original code was looping forwards.  When I encountered this same bug I thought it was because the deleted record was no longer part of the index and therefore I was skipping records.  Also I work with n-tier models and use custom object models a lot where it is always good practice to loop backwards if you are deleting.

I will get back to you once I've attempted your first suggestion.

Thanks,

Quatro
0
 
LVL 12

Expert Comment

by:roverm
ID: 6991724
Thanks for the points!

D'Mzzl!
RoverM
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question