Bizzare ADO Recordset issue 
Author Message
 Bizzare ADO Recordset issue

All,

I have written simple code below to query a simple Access table
(tblItineraryReport) that contains two fields (text, 50 chars), (memo) using
an ADO recordset that will be used with the rptMemberItinerary.  Without the
WORKAROUND code, the recordset returned contains duplicate records, every
other time mnuItineraryPrint_Click( ) event is fired.  It's almost like the
resources are not being freed up...I do not see any errors.  If I delete the
records before closing a freeing resources....it works correctly, every
time.  I have commented out the report code, ruling out the report as the
cause of the issue.  Has anyone seen this "flakiness" before and know why it
is occuring?  Bug???

I can give more detail as needed...

Thanks in advance.

Shawn

------------------------

Private Sub mnuItineraryPrint_Click()

    Dim nodPrint As MSComctlLib.Node
    Dim strNodeKey As String
    Dim intResponse As Integer
    Dim strNodeType As String
    Dim strMember As String
    Dim rstItineraryReport As ADODB.Recordset
    Dim strQuery As String

    On Error GoTo err_mnuItineraryPrint

    Set nodPrint = tvItinerary.SelectedItem

    ' Get node key
    strNodeKey = nodPrint.Key

    If (InStr(1, strNodeKey, ":", vbBinaryCompare) <> 0) Then

        ' Get node type
        strNodeType = Left$(strNodeKey, (InStr(1, strNodeKey, ":",
vbBinaryCompare) - 1))

        Select Case strNodeType

            Case "MEMBER"

                ' Set mouse to hourglass
                Screen.MousePointer = vbHourglass
                DoEvents

                strMember = nodPrint.Text

                Call PopulateMemberItinerary(strMember)

                Set rstItineraryReport = New ADODB.Recordset

                ' Build query
                strQuery = "SELECT * " & _
                           "FROM tblItineraryReport " & _
                           "ORDER BY [SequenceNumber];"

                rstItineraryReport.Open strQuery, gadoConnection,
adOpenDynamic, adLockOptimistic

*********************************************
START OF REPORT CODE
*********************************************
                With rptMemberItinerary

                    Set .DataSource = rstItineraryReport

                    .Orientation = rptOrientPortrait
                    .Title = Me.txtMeetingName.Text
                    .Caption = .Title

.Sections.Item("Section2").Controls.Item("lblMemberName").Caption =
strMember

                    ' Set mouse to default
                    Screen.MousePointer = vbDefault
                    DoEvents

                    .Show vbModal

                End With

*********************************************
END OF REPORT CODE
*********************************************

                rstItineraryReport.MoveFirst

*********************************************
START OF WORKAROUND
*********************************************
                ' Clear recordset...otherwise recordset appears to cache
info...SEL ???
                Do Until rstItineraryReport.EOF
                    rstItineraryReport.Delete
                    rstItineraryReport.MoveNext
                Loop

*********************************************
END OF WORKAROUND
*********************************************
                ' Close and free resources
                rstItineraryReport.Close
                Set rstItineraryReport = Nothing

            Case "TRAVEL"

            Case "HOTEL"

        End Select

    End If

    ' Set mouse to default
    Screen.MousePointer = vbDefault

    ' Free resources
    Set rstItineraryReport = Nothing

    Exit Sub

err_mnuItineraryPrint:

    MsgBox Err.Description, vbExclamation, "Error " & Err.Number

    ' Set mouse to default
    Screen.MousePointer = vbDefault

    ' Free resources
    Set rstItineraryReport = Nothing

End Sub



Sat, 21 May 2005 05:03:17 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. URGENT MICROSOFT SUPPORT ISSUE - using ADO recordsets bound to Access .mbd Formsat runtime

2. Need help with ADO recordset sorting issue

3. ADO recordset memory issue

4. ADO RECORDSET - update database issue

5. ADO Recordset and Connection Issues in VB6

6. Open ADO recordset on another ADO recordset - possible?

7. Recordset vs. ADO Recordset

8. How to change ADO recordset retrieved from Oracle into new recordset

9. ADO fails to release Recordset cursor after opening disconnected Recordset

10. 2 ado recordsets into a new 3rd recordset

11. Hierarchical ADO recordset -> ADO.NET Dataset Fill

12. Bind an ado recordset directly to an ado data control (adodc)

 

 
Powered by phpBB® Forum Software