Hi Kiran

ADO doesn't care what the SQL string is that you send through to the
database. It is just passing that to the back end and then returning the
results to you. So yes you can use a having statement in your query as long
as Oracle supports it. As Oracle supports standard SQL then you're OK on
that front.

I would guess that the error is telling you that no records are being
returned. This is probably down to one of two things:
1. Your query is valid and there are no duplicates in the table. , or
2. Your query is invalid and hence returning no records.

Are you sure there are duplicates in the database table? If there are dupes,
can you test your query string against the database in some other way?

I would suggest code something like the following (Note this is targetted at
the Northwind database in Access):

Sub test()

Const CONN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;Persist
Security Info=False"

Dim objRS As ADODB.Recordset
Dim objConn As ADODB.Connection

Dim strSQL As String
Dim lngRecordCount As Long

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

'open a connection explicitly
objConn.Open CONN

'build the SQL string
strSQL = "SELECT [Order Details].OrderID " & _
"FROM [Order Details] " & _
"GROUP BY [Order Details].OrderID " & _
"HAVING Count([Order Details].OrderID)1"

'open the recordset
objRS.Open strSQL, CONN, adOpenKeyset

'check for errors
If objConn.Errors.Count = 0 Then
'no errors

'check for returned records which have duplicate order ID's
lngRecordCount = objRS.RecordCount
If lngRecordCount 0 Then
MsgBox lngRecordCount & " duplicates found!"
Else
MsgBox "No duplicate records found!"
End If

Else
'SQL Errors returned, check the errors collection
'Note: Should loop through the errors collection to pickup all errors
MsgBox "There were errors." & vbLf & "Error 1: " &
objConn.Errors(0).Description
End If

End Sub

HTH

Barry


"ukp9999" wrote:


Barry,

i tried but getting the same error.
can we use have clause in adodb object?

thanks

Kiran


--
ukp9999
------------------------------------------------------------------------
ukp9999's Profile: http://www.excelforum.com/member.php...o&userid=28853
View this thread: http://www.excelforum.com/showthread...hreadid=486019