Empty Row When Using DISTINCT

From: Tim H. (anonymous_at_discussions.microsoft.com)
Date: 05/28/04 

Date: Fri, 28 May 2004 10:51:06 -0700

I have a readonly recordset that I open using the following SQL:

    Set mcnProject = New ADODB.Connection
    strConnString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & mstrProjectPathAndName
    Call mcnProject.Open(strConnString )

    sSql = " SELECT DISTINCT 'NA' AS OwnerID,'S' & NPPS.RNPFromLocation & 'to' & NPPS.RNPToLocation AS ID, "
    sSql = sSql & " NPPS.RNPFromLocation AS OriginatingPointRowNumber, "
    sSql = sSql & " NPPS.RNPToLocation AS TerminatingPointRowNumber, NPPS.SpanLength "
    sSql = sSql & " FROM NewPPS AS NPPS "
    sSql = sSql & " WHERE NPPS.RNPToLocation <> 0 "
    Call mrsSpans.Open(sSql, mcnProject, adOpenStatic, adLockReadOnly)
    Call MakeClunkyFixBecauseOfADOBug(mrsSpans, sSql)

The problem is that the table contains no matching rows for the WHERE criteria and yet the recordset shows one row, and all of its fields are empty. What I found was that by removing DISTINCT in the SQL I would get back zero records as desired exception on UNION queries where I had to take the extra step of removing and UNIONs in order to get zero records. I created a method to get around this but its very clunky. Is this a bug with ADO or am I possibly doing something wrong in the above code? And yes, the table, for sure, has no matching records for the criteria. If I drop the sql directly into and Access query and run it from Access, it works correctly. If I call that query from code, I get the same bad behavior mention above.

'-----Method to get around the problem:
Private Sub MakeClunkyFixBecauseOfADOBug(ByRef rs As ADODB.Recordset, ByVal sql As String)

    If rs.RecordCount = 1 Then
        If rs!OwnerID & "" = "" Then
            If InStr(1, UCase$(sql), "UNION") > 0 Then
                sql = Left(sql, InStr(1, UCase$(sql), "UNION") - 1)
            End If
            sql = Replace(UCase$(sql), "DISTINCT", "")
            If InStr(1, UCase$(sql), "WHERE") > 1 Then
                sql = sql & " AND True=False"
            Else
                sql = sql & " WHERE True=False"
            End If
            If rs.State = adStateOpen Then
                Call rs.Close
            End If
            Call rs.Open(sql, mcnProject, adOpenStatic, adLockReadOnly)
            Debug.Print rs.RecordCount
        End If
    End If

End Sub

Relevant Pages

  • Re: Column Heads
    ... to change my listbox rowsource from one stored procedure ... It's when I have both SQL ... >Private Sub Command2_Click ... >> and its AfterUpdate event fires to requery the listbox. ...
    (microsoft.public.access.formscoding)
  • Re: Change Address Fields with Combo on Form
    ... The reason is that the SQL uses to delimit the text fields ... MsgBox "Update Completed" ... Private Sub cmb_Addr_change_AfterUpdate ... Dim SQL_Text As String ...
    (microsoft.public.access.forms)
  • Re: datasource updates not persistent
    ... If you do have a rowset of changes, then use SQL Profiler (If you are ... I have a simple single table access database added to my project, ... Private Sub MotorsBindingNavigatorSaveItem_Click(ByVal sender As ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SQL Syntax for CDate Function
    ... The Format property of my text box ('Text0' in this ... Private Sub Command2_Click ... > I have tried every version of syntax I can think of to make the SQL ... > SQL syntax issue. ...
    (microsoft.public.access.queries)
  • Re: Shading
    ... Not SQL. ... A few lines of VBA in the Format event of the report's Detail ... Private Sub Detail_Format ... > I want to shade all the Sally's in the report. ...
    (microsoft.public.access.reports)