Solved

ADO Bug?

Posted on 2002-05-04
6
232 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
  • 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now