FIX: Getting SELECT DISTINCT to Work Under ADO with SQL Server

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a standard .exe project, and from the Project menu, choose References, then select Microsoft ActiveX Data Objects 1.5 Library.
2. Add 2 command buttons to the form and paste the following code in the Declaration section of the form:
   
   Note You must change Username=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

      Option Explicit
   
      Dim conn As New Connection
      'Dim com As New Command  'Uncomment to make DISTINCT work.
      Dim rs As New Recordset
   
    
      Private Sub Command1_Click()
   
        conn.open "mymachine", "<username>", "<strong password>"
        conn.cursorlocation = adUseClient  'Change to aduseServer.
        'com.ActiveConnection = conn  'Uncomment to make DISTINCT work.
   
        rs.open "select distinct state from authors", conn, _
            adOpenKeyset, adLockOptimistic
           MsgBox rs.RecordCount
      End Sub
   
      Private Sub Command2_Click()
        Dim counter As Integer
        counter = 0
           conn.Open "dsn=mymachine;Username=<username>;PWD=<strong password>;"
           com.ActiveConnection = conn
           conn.CursorLocation = adUseClient   'Comment this
           'conn.CursorLocation = adUseServer  'and uncomment this.
           com.CommandText = "select distinct state from authors"
           com.CommandType = adCmdText
           rs.CursorType = adOpenKeyset
           'cursortype will be adopenforwardonly with adUseServer.
           Set rs = com.Execute
           While Not rs.EOF
             Debug.Print rs!State
             counter = counter + 1
             rs.MoveNext
           Wend
           Debug.Print "--------------------"
           MsgBox counter  'recordcount return -1 with adUseServer
      End Sub
   					

3. Run the code and click Command1. Note the number of records returned. Change adUseClient to adUseServer in the CursorLocation. Run the code again and fewer records are returned if there are duplicate states.
4. Change aduseServer back to adUseClient. Uncomment the Dim of the command object and the code in Command1. Run the code again. You receive the correct number of records using this technique also.NOTE: If you set the recordset properties ahead of time and use the syntax Rs.open com the recordcount returned will be incorrect. However, to get the correct recordcount using the Rs.Open method you must use a command object and set its active connection.
   
   
5. Run the code again and click Command2. Note the counter. Comment the CursorLocation for adUseClient and uncomment the adUseServer CursorLocation. Run the code and note the counter again.