Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete object (Access 2000)

    I am deleting a series of objects where only the last two letters differ.Is it possible to build only one line of command to delete all the objects having one and the same beginning "qryOnStock" ?

    For example :
    DoCmd.DeleteObject "QryOnStockSo"
    DoCmd.DeleteObject "QryOnStockVa"
    DoCmd.DeleteObject "QryOnStockTr"
    etc etc

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

    Re: Delete object (Access 2000)

    No, you can't do that in one line. But you can create a procedure that loops through the collection of queries and deletes the queries whose name begins with a specified text:
    <code>
    Sub DeleteQueriesBeginningWith(strName As String)
    Dim i As Integer
    Dim dbs As DAO.Database
    Dim qdfs As DAO.QueryDefs

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set qdfs = dbs.QueryDefs
    For i = qdfs.Count - 1 To 0 Step -1
    If Left(qdfs(i).Name, Len(strName)) = strName Then
    qdfs.Delete qdfs(i).Name
    End If
    Next i

    ExitHandler:
    Set qdfs = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    Use it like this:
    <code>
    Sub DeleteQueries()
    DeleteQueriesBeginningWith "qryOnStock"
    End Sub
    </code>
    Be careful: test on a copy of your database first.

Posting Permissions

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