Solved

ADO Bug?

Posted on 2002-05-04
6
239 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month9 days, 23 hours left to enroll

624 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