VBA excel - problem with having clause in sql with adodb.connection/recordset

Hi

i need a help on the following error

i am getting error in VBA excel when i am using having clause in the sql.
i used adodb.connection and adodb.recordset.

my problem is, i need to check duplicates in the oracle table.
so i used the sql statement as follows

select count(*) from table-1
having count(*) > 1
group by field-1,field-2,field-3.

and string the count in a variable.

var-1 = rs.fields(0).

when i execute that i am getting error as runtime error : 3021
and here is the description

" Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires current record"

here is my code

strsql = "select name,product,product_number,begin_date,end_date from product_lisence_info" _
& " having count(*) > 1 " _
& " group by name,product,product_number,begin_date,end_date "


rs1.Open strsql, con1, adOpenKeyset

dupli = rs1.Fields(0)


rs1.close


thanks

Kiran

Hi Kiran

The having clause should be at the end

Select ...
From ...
Group By ...
Having ...

Try reorganising your query so that it is this way around. Not sure about
your error though, souunds like there may be something else going on as well
but that should be a start.

Thanks

Barry



"ukp9999" wrote:

>
> Hi
>
> i need a help on the following error
>
> i am getting error in VBA excel when i am using having clause in the
> sql.
> i used adodb.connection and adodb.recordset.
>
> my problem is, i need to check duplicates in the oracle table.
> so i used the sql statement as follows
>
> select count(*) from table-1
> having count(*) > 1
> group by field-1,field-2,field-3.
>
> and string the count in a variable.
>
> var-1 = rs.fields(0).
>
> when i execute that i am getting error as runtime error : 3021
> and here is the description
>
> " Either BOF or EOF is True, or the current record has been deleted.
> Requested operation requires current record"
>
> here is my code
>
> strsql = "select name,product,product_number,begin_date,end_date from
> product_lisence_info" _
> & " having count(*) > 1 " _
> & " group by
> name,product,product_number,begin_date,end_date "
>
>
> rs1.Open strsql, con1, adOpenKeyset
>
> dupli = rs1.Fields(0)
>
>
> rs1.close
>
>
> 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
>
>

Barry,

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

thanks

Kiran

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
>
>