Clean Up Duplicates In Access 2000 ADO DB

Hello,

I am using an Access 2000 database with an ADO connection. The code I use to connect is:


Option Explicit
Private WithEvents connSettings As ADODB.Connection
Private WithEvents rsFileList As ADODB.Recordset

Set connSettings = New ADODB.Connection
connSettings.CursorLocation = adUseClient
connSettings.Open strConnect
Set rsFileList = New ADODB.Recordset
rsFileList.CursorType = adOpenStatic
rsFileList.CursorLocation = adUseClient
rsFileList.LockType = adLockPessimistic
rsFileList.Source = "Select * From FileList"
rsFileList.ActiveConnection = connSettings
rsFileList.Open


What I want to do is clean up the table of all duplicate entries. I tried looping through them but it really didn't work. If anyone could help that would be great!

Thanks a lot!

Hi LaXkId842

If you open up your database in access and go to queries. Create the find duplicates query from the query wizards, using the table you need. Then paste the generated SQL into your VB ( rsFileList.Source =) and loop through deleteing each record. Finally change your recordset cursor type to adOpenDynamic, as adOpenStatic doesn't let you alter records.

Hope this helps

Ian