Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Objects not being deleted (2000sp3)

    I have to run this code 7 or 8 times before all the queries with names beginning "temp" are deleted - yet I do not receive any messages. Why doesn't it work?

    Public Sub cleanup()
    Dim db As DAO.Database, tdf As DAO.TableDef, qdf As DAO.QueryDef
    Set db = CurrentDb
    For Each qdf In db.QueryDefs
    If Left(qdf.Name, 4) = "temp" Then
    db.QueryDefs.Delete qdf.Name
    End If
    Next
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Objects not being deleted (2000sp3)

    Would something like this work?:

    ...

    Dim DB As Database
    Dim qdf As QueryDef

    For Each qdf In CurrentDb.QueryDefs
    If Left(qdf.Name, 4) = "Temp" Then
    DoCmd.DeleteObject acQuery, qdf.name
    End If
    Next


    ....
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Objects not being deleted (2000sp3)

    In general, it's best to loop backwards through a collection if you want to delete members, since the collection is changing as you do so. Compare it to wiping the floor - it's better to walk backwards as you do so.

    Dim db As DAO.Database, qdf As DAO.QueryDef
    Dim i As Integer
    Set db = CurrentDb
    For i = db.QueryDefs.Count - 1 To 0 Step -1
    Set qdf = db.QueryDefs(i)
    If Left(qdf.Name, 4) = "temp" Then
    db.QueryDefs.Delete qdf.Name
    End If
    Next i

Posting Permissions

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