Delete Duplicate Records Sql Server Backend (1 Viewer)

jsdba

Registered User.
Local time
Today, 10:52
Joined
Jun 25, 2014
Messages
165
I found the code below at this link. I'm trying to delete duplicated records in a table. I've tested it using a local table but and it works perfect. However i get an error (ODBC call failed) when i run this code using sql server tables. The highlighted part of the code is what i added in hopes that was the cause if the error. it wasn't. Any ideas?

https://www.databasejournal.com/features/msaccess/article.php/3077791/Delete-Duplicate-Records-From-Access-Tables.htm

Code:
Sub DeleteDuplicateRecords(strTableName As String)
    ' Deletes exact duplicates from the specified table.
    ' No user confirmation is required. Use with caution.
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    Dim varBookmark As Variant

    Set tdf = DBEngine(0)(0).TableDefs(strTableName)
    strSQL = "SELECT * FROM " & strTableName & " ORDER BY "
    ' Build a sort string to make sure duplicate records are
    ' adjacent. Can't sort on OLE or Memo fields,though.
    For Each fld In tdf.Fields
        If (fld.Type <> dbMemo) And _
         (fld.Type _
         <> dbLongBinary) Then
            strSQL = strSQL & fld.Name & ", "
        End If
    Next fld
    ' Remove the extra comma and space from the SQL
    strSQL = Left(strSQL, Len(strSQL) - 2)
    Set tdf = Nothing

    Set rst = CurrentDb.OpenRecordset(strSQL, [B][COLOR="Red"]dbOpenDynaset, dbSeeChanges[/COLOR][/B])
    Set rst2 = rst.Clone
    rst.MoveNext
    Do Until rst.EOF
        varBookmark = rst.Bookmark
        For Each fld In rst.Fields
            If fld.Value <> rst2.Fields(fld.Name).Value Then
                GoTo NextRecord
            End If
        Next fld
        rst.Delete
        GoTo SkipBookmark
NextRecord:
        rst2.Bookmark = varBookmark
SkipBookmark:
        rst.MoveNext
    Loop
End Sub
 

jsdba

Registered User.
Local time
Today, 10:52
Joined
Jun 25, 2014
Messages
165
If my problem isn't clear in some way i can give me details if necessary. i just think theres something missing from the code in order to get it work on sql server backend tables. Please help
 

isladogs

MVP / VIP
Local time
Today, 14:52
Joined
Jan 14, 2017
Messages
18,186
Dbseechanges is required in any recordset for a sql table where any updates or deletions are involved. So I think you do need that

Add error handling to the code then tell us which line it fails on and the error number
Have you tried stepping through the code and adding debug lines to what is happening at each step?
 

jsdba

Registered User.
Local time
Today, 10:52
Joined
Jun 25, 2014
Messages
165
I found the code below at this link. I'm trying to delete duplicated records in a table. I've tested it using a local table but and it works perfect. However i get an error (ODBC call failed) when i run this code using sql server tables. The highlighted part of the code is what i added in hopes that was the cause if the error. it wasn't. Any ideas?

https://www.databasejournal.com/features/msaccess/article.php/3077791/Delete-Duplicate-Records-From-Access-Tables.htm

Code:
Sub DeleteDuplicateRecords(strTableName As String)
    ' Deletes exact duplicates from the specified table.
    ' No user confirmation is required. Use with caution.
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    Dim varBookmark As Variant

    Set tdf = DBEngine(0)(0).TableDefs(strTableName)
    strSQL = "SELECT * FROM " & strTableName & " ORDER BY "
    ' Build a sort string to make sure duplicate records are
    ' adjacent. Can't sort on OLE or Memo fields,though.
    For Each fld In tdf.Fields
        If (fld.Type <> dbMemo) And _
         (fld.Type _
         <> dbLongBinary) Then
            strSQL = strSQL & fld.Name & ", "
        End If
    Next fld
    ' Remove the extra comma and space from the SQL
    strSQL = Left(strSQL, Len(strSQL) - 2)
    Set tdf = Nothing

    Set rst = CurrentDb.OpenRecordset(strSQL, [B][COLOR="Red"]dbOpenDynaset, dbSeeChanges[/COLOR][/B])
    Set rst2 = rst.Clone
    rst.MoveNext
    Do Until rst.EOF
        varBookmark = rst.Bookmark
        For Each fld In rst.Fields
            If fld.Value <> rst2.Fields(fld.Name).Value Then
                GoTo NextRecord
            End If
        Next fld
       [COLOR="Yellow"] rst.Delete[/COLOR] <<this line fails 
        GoTo SkipBookmark
NextRecord:
        rst2.Bookmark = varBookmark
SkipBookmark:
        rst.MoveNext
    Loop
End Sub

DBSeeChanges is in the code. It think this code was written for local tables.
 

isladogs

MVP / VIP
Local time
Today, 14:52
Joined
Jan 14, 2017
Messages
18,186
Yes I know its there - I was confirming you need that.

I hadn't seen the yellow text earlier as it doesn't show up well.
It may just be me but I suggest you change that colour.

There's nothing obvious I can see that would only work for local tables.
Try using similar code to delete a single record in another table.
Then report back
 

sonic8

AWF VIP
Local time
Today, 15:52
Joined
Oct 27, 2015
Messages
998
There's nothing obvious I can see that would only work for local tables.
There is!
The code finds duplicates where all fields are identical. All fields can only be identical if there is no primary key. If there is no primary key in an ODBC linked table it is read-only.
 

jsdba

Registered User.
Local time
Today, 10:52
Joined
Jun 25, 2014
Messages
165
There is!
The code finds duplicates where all fields are identical. All fields can only be identical if there is no primary key. If there is no primary key in an ODBC linked table it is read-only.

Yes i had an interesting situation with the PK for this table. this table uses a composite key. I imported this table from another sql server database then linked it to access. I lost the PK when importing and some duplicated records were entered.

Ultimate i created a local table ran the code on that. Fixed the PK in the table then imported the data in the back into the linked tabled.

Thanks for the help sonic8
 

Users who are viewing this thread

Top Bottom