Solved

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

Posted on 2002-04-08
9
399 Views
Last Modified: 2008-02-26
Hello all.
Is there a way to find out if field in an ADO recordset contains unique values (No Duplicates).
Thanks
Dean.
0
Comment
Question by:Dwhite
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
I think there is no way to do it except a loop will do..
0
 
LVL 2

Expert Comment

by:skhorshid
Comment Utility
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
 

Author Comment

by:Dwhite
Comment Utility
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
 
LVL 2

Expert Comment

by:skhorshid
Comment Utility
why dont you change your sql ??

select distinct  col1, col2 from table where col1>col2
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:deighton
Comment Utility
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
 
LVL 1

Expert Comment

by:riaz9
Comment Utility
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
 
LVL 5

Accepted Solution

by:
kcm76 earned 200 total points
Comment Utility
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
 
LVL 5

Expert Comment

by:kcm76
Comment Utility
to: riaz
My logic is completly different from urs.

KCM
0
 

Author Comment

by:Dwhite
Comment Utility
Hank you all for the help.
Dean.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now