Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    9

    Unanswered: Strange table deletion issue

    I have routine which uses 4 temporary tables named with tmp prefixes.
    The routine below is used to tidy up at the end and remove them.
    If I use db.TableDefs.Delete tdf.Name the removal doesn't work successfully. It removes 2 of the four but stubbornly refuses to remove the other 2.
    However if I run the Sub on its own it will remove 1 more and then again 1 more if I run it again!

    Using the DelTable sub removes all no problem at all.
    I am puzzled as to why there is a problem with db.TableDefs.Delete tdf.Name. Particularly since it "works" if you run it 3 times in total!

    The only thing I can think of is that the 2 tables that refuse to go first time are both used to produce recordsets within the main routine but these are closed and nulled at the end before any deletion attempt. Even so when I can see both tables in the table list and they will still only go 1 at a time as described above I have no understanding of the issue.

    Any suggestions?
    Thanks.

    Code:
    Public Sub DelTmpTables()
    
    Dim tdf As TableDef
    Dim db As Database
    Set db = CurrentDb
    db.TableDefs.Refresh
    For Each tdf In db.TableDefs
    
    If LCase(Left(tdf.Name, 3)) = "tmp" Then
    
    Debug.Print tdf.Name
    REM Next line works fine
    DelTable tdf.Name
    REM The next line doesn't work fully
    REM db.TableDefs.Delete tdf.Name
    
    End If
    Next
    
    End Sub
    
    Sub DelTable(tblName As String) As Boolean
     
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable, tblName
        DoCmd.SetWarnings True
    
    End Sub

  2. #2
    Join Date
    May 2012
    Posts
    9

    Strange table deletion issue

    I have routine which uses 4 temporary tables named with tmp prefixes.
    The routine below is used to tidy up at the end and remove them.
    If I use db.TableDefs.Delete tdf.Name the removal doesn't work successfully. It removes 2 of the four but stubbornly refuses to remove the other 2.
    However if I run the Sub on its own it will remove 1 more and then again 1 more if I run it again!

    Using the DelTable sub removes all no problem at all.
    I am puzzled as to why there is a problem with db.TableDefs.Delete tdf.Name. Particularly since it "works" if you run it 3 times in total!

    The only thing I can think of is that the 2 tables that refuse to go first time are both used to produce recordsets within the main routine but these are closed and nulled at the end before any deletion attempt. Even so when I can see both tables in the table list and they will still only go 1 at a time as described above I have no understanding of the issue.
    Any suggestions?
    Thanks
    Code:
    Public Sub DelTmpTables()
    
    Dim tdf As TableDef
    Dim db As Database
    Set db = CurrentDb
    db.TableDefs.Refresh
    For Each tdf In db.TableDefs
    
    If LCase(Left(tdf.Name, 3)) = "tmp" Then
    
    Debug.Print tdf.Name
    REM Next line works fine
    DelTable tdf.Name
    REM The next line doesn't work fully
    REM db.TableDefs.Delete tdf.Name
    
    End If
    Next
    
    End Sub
    
    Sub DelTable(tblName As String) As Boolean
     
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable, tblName
        DoCmd.SetWarnings True
    
    End Sub

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    Public Sub DelTmpTables()
    
        Dim tdf As TableDef
        Dim db As Database
    
        Set db = CurrentDb
        For Each tdf In db.TableDefs
            If LCase(Left(tdf.Name, 3)) = "tmp" Then
                db.TableDefs.Delete tdf.Name
                db.TableDefs.Refresh
            End If
        Next
    End Sub
    Have a nice day!

  4. #4
    Join Date
    May 2012
    Posts
    9
    Thanks for suggestion.
    I thought only 1 refresh was needed before traversing the Tabledefs so I have now tried it inside the for loop with exactly the same result.
    I have no problem using the alternative that works but I don't like being unable to understand why the other code doesn't work as expected.

    John B

Posting Permissions

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