Solved: Rename table problem

[Movian]

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.

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

[hansup]

I don't understand how SQL Server fits into this. Is tbllookupvalues a native Access table or a link to a SQL Server table?

Even though I don't understand what's going on, I'll suggest you see whether TableDefs.Refresh allows tbllookupvalues2 to be recognized.

[Movian]

My access front end connects to SQL express 2008 for ALL its tables.
tblLookupValues is a custom table i use for userdefinable drop down lists.

Isn't TableDefs Dao Specific? also im not working with a specific recordset so there are no tabledefs TO refresh. Its the fact that it generates the error then when i press play again it works fine.....

[hansup]

In an Access MDB or ACCDB, all tables whether linked or native local tables exist as items in the TableDefs collection.

However, if your Access application is an ADP, I don't know what rules apply.

Meanwhile, since tbllookupvalues is a SQL Server table, I think you should clean out the duplicate rows and then add a unique constraint to prevent any more duplicates. If you can do that, the problem you're trying to solve now goes away.

[Movian]

hmmm...

I will look at that but ther is a seperate primary key and the uniqueness would be between 3 seperate fields

Form, Control and value

all three together need to be unique....

I may look at doing that in the future. For now i fixed it by switching over to T-SQL commands to achieve the same..


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.RunSQL "DROP Table tbllookupvalues"
'DoCmd.DeleteObject acTable, "tbllookupvalues"

DoCmd.RunSQL "sp_RENAME 'tbllookupvalues2', 'tbllookupvalues'"
'DoCmd.Rename "tbllookupvalues", acTable, "tbllookupvalues2"
DoCmd.SetWarnings True
MsgBox "Process Complete", vbInformation
Else
MsgBox "Duplicate removal cancled", vbInformation, "Canceled"
End If
End Sub

[hansup]

hmmm...

I will look at that but ther is a seperate primary key and the uniqueness would be between 3 seperate fields

Form, Control and value

all three together need to be unique....

I may look at doing that in the future. For now i fixed it by switching over to T-SQL commands to achieve the same..


The separate primary key is a non-issue. This is really quick and easy. Just create a unique index on Form, Control and value (after you first discard the duplicate rows).

USE [your_db]
GO

CREATE UNIQUE NONCLUSTERED INDEX [uniq_form_control_value] ON [dbo].[tbllookupvalues]
(
[Form] ASC,
[Control] ASC,
[value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO