ADO recordset query quandery locating duplicates

[James Snyder]

I am having trouble setting up a query for a recordset that will list duplicates for an automated process to clean up. I am using Excel 2010 with Microsoft ActiveX Data Objects Recordset 6.0 Library and Microsoft ActiveX Data Objects 6.1 Library as references.

So far, I have chopped the query down to it's most basic elements and reassembled until it broke. Necessary pieces work well, but when I combine them, I get nowhere.

Here is the working simple query to retrieve a single row:

    dupeSQL = "SELECT PSID FROM [Obstructed$] "

Here is the working single query to retrieve a count:

     dupeSQL = "SELECT COUNT(PSID) As '" & "CountPSID" & "' " _
        & "FROM [Obstructed$] "

When combined, I cannot figure out why it won't work:

    dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
        & "FROM [Obstructed$] "

Here is what I am trying to get to:

    dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
        & "FROM [Obstructed$] " _
        & "WHERE COUNT(PSID) > 1 " _
        & "GROUP BY PSID"

I can even work without the GROUP BY. I have also played with the HAVING clause to try and accommodate the [PSID] field, but have gotten nowhere.

 

[James Snyder]

Although I would still be happy to learn the answer, I have abandoned ADO as a tool for this project.