Consulting

Results 1 to 6 of 6

Thread: Solved: Rename table problem

  1. #1
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399

    Solved: Rename table problem

    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]
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  2. #2
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    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.

  3. #3
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399
    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.....
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  4. #4
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    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.

  5. #5
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399
    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..


    [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.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[/VBA]
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  6. #6
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by 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..
    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).

    [VBA]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[/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •