Hey,
When we do updates for our system we sometimes get eronious duplicates in one of our tables. I have setup a sub that removes the duplicates by selecting distinct into a new table, then deleting the old table and then renaming the new table to the old table name.... HOWEVER when the system goes to rename the new table to the old name it throws an error stating it can't find the object. If i hit debug and then press play it then finds it and renames it normaly.... at first i thought it was just taking more time to do somthing to get it setup so i setup a Sleep command but no matter how long i set the sleep for the result is still the same..... I am connected to SQL server 2008 R2 for the backend tables...
As always any thoughts/help are apriciated.
[VBA]Private Sub FixDuplicateDropDowns()
If MsgBox("Are you sure you want to remove all duplicate Drop Down Entries", vbYesNo, "Remove Duplicates?") = vbYes Then
Dim SQLString As String
SQLString = "SELECT DISTINCT * INTO tbllookupvalues2 FROM tbllookupvalues"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLString
DoCmd.DeleteObject acTable, "tbllookupvalues"
DoEvents
Sleep 5000
DoCmd.Rename "tbllookupvalues", acTable, "tbllookupvalues2"
DoCmd.SetWarnings True
MsgBox "Process Complete", vbInformation
Else
MsgBox "Duplicate removal cancled", vbInformation, "Canceled"
End If
End Sub[/VBA]