Error Running Find Duplicates Query using ADO

[iheartvba]

To put the below code simply, I have created a find duplicates query for a table using the Access query wizard. I have then tried to plug the Sql String (strSqlDupColl) copied from the Query Builder into VBA, using ADO I have tried to call the Query, but I get the following error : Invalid SQL statement expected; ‘DELETE’,’INSERT’,'UPDATE' or SELECT". I have no idea why this is occuring. Please help.

Option Compare Database
Option Explicit
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Public strSubject As String
Public strBody As String
 
Private Sub DupChk()
Dim btCount As Byte
Dim strSqlDupColl As String
Set cnn = CurrentProject.Connection
strSqlDupColl = "SELECT First(tblCollections.[CollectionID]) AS Coll, " & _
"First(tblCollections.[OfficeID]) AS Off, Count(tblCollections.[CollectionID]) AS DupsNum " & _
"FROM tblCollections " & _
"GROUP BY tblCollections.[CollectionID], tblCollections.[OfficeID] " & _
"HAVING (((Count(tblCollections.[CollectionID]))>1) AND ((Count(tblCollections.[OfficeID]))>1));"
rst.Open "strSqlDupColl", cnn, adOpenDynamic, adLockOptimistic
    With rst
        If rst.BOF And rst.EOF And btCount <> 1 Then
            ImportUpd
                btCount = 1
        Else
        strSubject = "Duplicate in tblCollections"
         SendEmail
        End If

[MikeTheBike]

@iheartvba
Hi
Without looking too far into the code, I suggest first you remove the Quote marks round the query variable, ie. this

rst.Open "strSqlDupColl", cnn, adOpenDynamic, adLockOptimistic

should be this

rst.Open strSqlDupColl, cnn, adOpenDynamic, adLockOptimistic

??

MTB

[iheartvba]

sorry about that mike, that was just me trying different things to get the code to work. Originally there were no quotes around strSqlDupColl and it was still not working.

Thanks

[ADezii]

@iheartvba
Before you attempt anything else, remove the Quotations from the strSqlDupColl String Variable, because ADO will interpret it as a Literal String, Replace Line #17 with:

rst.Open strSqlDupColl, cnn, adOpenDynamic, adLockOptimistic 

[ADezii]

@MikeTheBike
Sorry MikeTheBike, didn't realize that I was stepping on your toes.

[ADezii]

@iheartvba
Can you Attach the Database, or a Subset of it?

[FishVal]

@iheartvba
But did you get the same error message when there was no quotes?

[MikeTheBike]

@ADezii
Hi ADezil

There does not seem to be any damage, all toes seem to be functioning OK, but thanks for the concern!

Back on topic, iheartvba, an answer to FishVals question would be of some help.

MTB

[iheartvba]

Ummm, it seems to be working now. I could very well have been putting quotes around the variables, sorry guys! Thanks for your help, it is greatly appreciated.

[iheartvba]

@iheartvba
Actually I was right it still isn't working when I put a query made via the find duplicates query wizard via ADO, but I have just done my own find duplicates query, so It would be ok if someone wants to close this topic (sorry I don't know how to close the topic)

Thanks every1 for your help