Remove duplicates using SQL DISTINCT

[tiredofit]

This code is taken from here:

https://www.ozgrid.com/forum/forum/tip-tricks-code-no-questions/advanced-excel-integration/7960-create-a-unique-list-with-ado-sql-from-closed-workbooks-xl

It removes duplicates using the SQL command DISTINCT.

The problem is it works for a small column of data, say 10 values but for something like 300,000 values, it returns only a few thousand records, when the true value should be 150,000.

What is wrong?

Option Explicit
Sub Create_Unique_List()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String, stSQL As String
Dim i As Long, lnMode As Long
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=" & wbBook.FullName & ";" _
        & "Extended Properties=""Excel 8.0;HDR=YES"";"
'The keyword DISTINCT generate a unique list in the SQL-statement."
'All the column in the worksheet Sheet1 have fieldnames in the first row
'which we all use here.
stSQL = "SELECT DISTINCT Data FROM [Sheet1$]"
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
cnt.Open stCon
rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic
'A check to see that records actually exist.
If Not rst.EOF Then
With Application
.ScreenUpdating = False
'Collect the present calculation-mode.
lnMode = .Calculation
.Calculation = xlCalculationManual
'Add a new worksheet
Worksheets.Add Before:=wsSheet
'Copy the records to the added worksheet.
ActiveSheet.Cells(2, 1).CopyFromRecordset rst
'Reset the calculation-mode.
.Calculation = lnMode
.ScreenUpdating = True
End With
Else
MsgBox "No records could be find!", vbCritical
End If
'Cleaning up
If CBool(rst.State And adStateOpen) Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) Then cnt.Close
Set cnt = Nothing
End Sub

 

[tiredofit]

You've got mixed data types in the same column, which really don't play well with ADO. Because the first several thousand rows are numbers, it then treats all the later non-numeric data as Null.

Thanks for the feedback.

So I suppose using a dictionary (or collection) is the preferable method.

 

[RoryA]

Yes. To be honest I'd expect it to be faster anyway.

 

[mole999]

could it be forced
SELECT
'''&ColA try to add a text leader to every one of those cells to prevent numbers being the default ?
From
Datatable
WHER

 

[RoryA]

No, that won't work. It will already be Null by that point.

 

[mole999]

so this isn't pulling the records
stSQL = "SELECT DISTINCT Data FROM [Sheet1$]"

I use it, but not familiar with all the wrinkles

 

[RoryA]

Nope. It can't "see" any of the non-numeric records.

 

[Kyle123]

You could save it as csv and use an ini file