Find out if a field in an ADO Recordset contains unique values.

Hello all.
Is there a way to find out if field in an ADO recordset contains unique values (No Duplicates).
Thanks
Dean.
DwhiteAsked:
Who is Participating?
 
Chandramouli kConnect With a Mentor ArchitectCommented:
Pls try this.

The following code uses two recordsets.

Use the First recordset to get all columns in the table. Then for each fld in the recordset run a Distinct-Count(*) query. If the Recordset Contains any records it is not unique Otherwise it is.


ALL THE BEST
KCM.

************SOURCE CODE*******************
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim strFldName As String, strQry As String
Private Sub Command1_Click()
rs.Open "Select * From Rb_format_ro", cn, 0, 1
If Not (rs.BOF And rs.EOF) Then
    For Each fld In rs.Fields
        MsgBox fld.Name
        strFldName = fld.Name
        strQry = "SELECT DISTINCT (" & strFldName & "), Count(*) AS TotCount" _
        & " From RB_FORMAT_RO " _
        & " GROUP BY " & strFldName _
        & " HAVING Count(*) > 1 "
       
        If rs1.State = 1 Then rs1.Close
        rs1.Open strQry, cn, 0, 1
        If Not (rs1.BOF And rs1.EOF) Then
            MsgBox "The Field " & strFldName & " Does not Contain Unique Records"
        Else
            MsgBox "The Field " & strFldName & " Contains Unique Records"
        End If
    Next fld
End If
rs1.close
set rs1 = nothing
rs.close
set rs = nothing
End Sub

Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\Exp\Vb\test.mdb"
End Sub

*******************
0
 
Ryan ChongCommented:
I think there is no way to do it except a loop will do..
0
 
skhorshidCommented:
you should really do that in your SQL that you use to generate the recordset, ie

Select distinct field from table
 

I would perform this operation in SQL for sure.

I dont know exactly what your are trying to do and your table structures however you I imagine you should use  count(), group by and distinct to perform your evaluations
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
DwhiteAuthor Commented:
Because the "Filter property" in ADO does not support Column to column comparison's e.g. Column1 > Column2.
I want to write a function that will do the same thing.
like.
Do until rst.EOF
if Column1 > Column2 then
 strFilter = strFilter & " And " & myUniqueColumnValue....

rst.MoveNext
loop
0
 
skhorshidCommented:
why dont you change your sql ??

select distinct  col1, col2 from table where col1>col2
0
 
deightonprogCommented:
apply the following sql query when you open a recordset - of course your table & field are not grading and name, so you need to change that bit.

.eof = true  indicates that there are no duplicates, .eof = false then you have duplicates

SELECT grading.name
FROM grading group by grading.name having count(grading.name) > 1
0
 
riaz9Commented:
You can execute 2 sql statements and compare the count(*) values like :

1. select count(*) from table - get count of records in recordset.

2. select count(distinct unique_column_name) from table - get count of records.

If both count() values are same,then unique ,else not.
0
 
Chandramouli kArchitectCommented:
to: riaz
My logic is completly different from urs.

KCM
0
 
DwhiteAuthor Commented:
Hank you all for the help.
Dean.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.