Code check, please

[ExcelChampion]

I cannot figure out what is wrong with this code. I get the error, "Object variable or With block variable not set."

I just don't see the problem...

Sub remDuplicates()
    Dim sSql As String
    Dim RFQ_NUM As String
    Dim REVISION As String
    Dim DOC_LINK As String
    Dim DATE_ISSUED As String
    Dim lRows As Long
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    sSql = "Select RFQ_NUM, REVISION, DOC_LINK, DATE_ISSUED " _
         & "From tbl_Docs " _
         & "Group By RFQ_NUM, REVISION, DOC_LINK, DATE_ISSUED " _
         & "Having Count(*) > 1"
    
    Set rs = New ADODB.Recordset
    
    With rs
        .ActiveConnection = CurrentProject.AccessConnection
        .CursorLocation = adUseClient
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Source = sSql
        .Open
        
        While Not .EOF
            sRFQ_NUM = .Fields(0).Value
            sREVISION = .Fields(1).Value
            sDOC_LINK = .Fields(2).Value
            sDATE_ISSUED = .Fields(3).Value
            
            sSql = "Delete*  " _
                 & "From tbl_Docs " _
                 & "Where RFQ_NUM = '" & sRFQ_NUM & "' " _
                 & "And REVISION = '" & sREVISION & "' " _
                 & "And DOC_LINK = '" & sDOC_LINK & "' " _
                 & "And " & sDATE_ISSUED & "<> " _
                 & "(Select Min(" & sDATE_ISSUED & ") " _
                 & "From tbl_Docs " _
                 & "Where RFQ_NUM = '" & sRFQ_NUM & "' " _
                 & "And REVISION = '" & sREVISION & "' " _
                 & "And DOC_LINK = '" & sDOC_LINK & "')"
                 

            con.Execute sSql, lRows
            
            
            .MoveNext
        Wend
    End With
    
    rs.Close
    Set rs = Nothing
    Set con = Nothing
End Sub

Thanks for taking a look.

Todd

 

[smozgur]

Which line is highlighted with the error?

 

[ExcelChampion]

Craps out here: con.Execute sSql, lRows

I'm thinking its a grammatical error somewhere...missing comma, asterisk, ??? But maybe its something else I'm over looking.

Thanks.

 

[Joe4]

I would say it is most likely an issue with your SQL code. Shouldn't there be a space between the "Delete" and "*"?

One trick I often use in these cases is to return sSql to a MsgBox and review your SQL code, to see if it looks right.
Try running that same code in a Access Query (going into SQL code and typing that exact same code in there).
Does that work? If it does, your SQL code should be valid.
If not, play around with it and get it working in the Query first. Then, go to SQL View and note the code. This what your VBA code needs to mirror.

 

[smozgur]

I would say it is most likely an issue with your SQL code. Shouldn't there be a space between the "Delete" and "*"?

I agree, it cannot return effected row count and likely cause object variable not set error.