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

    delete records with ending 1? (Access 2000)

    Can i delete all the tables with the ending 1? I am appending 12 tables that get the ending 1,
    for example orders1, products1, etc. After appendfing and updating i want to delete all the tables
    with the ending 1,but i do not want to delete tables that do not have such an ending. Is it possible?

    I have a function for deleting all the tables,but i do not need it for my case:

    Function DeleteTables()
    Dim dbs As DAO.Database
    Dim i As Integer
    Set dbs = CurrentDb

    ' Loop backwards through relations
    For i = dbs.Relations.Count - 1 To 0 Step -1
    dbs.Relations.Delete dbs.Relations(i).Name
    Next i

    ' Loop backwards through tabledefs
    For i = dbs.TableDefs.Count - 1 To 0 Step -1
    If Left(dbs.TableDefs(i).Name, 4) <> "MSys" Then
    dbs.TableDefs.Delete dbs.TableDefs(i).Name
    End If
    Next i

    Set dbs = Nothing
    End Function

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

    Re: delete records with ending 1? (Access 2000)

    Try this variation (and compare it to the original to understand what it does):

    Function DeleteTablesEnding1()
    Dim dbs As DAO.Database
    Dim i As Integer
    Set dbs = CurrentDb

    ' Loop backwards through tabledefs
    For i = dbs.TableDefs.Count - 1 To 0 Step -1
    If Right(dbs.TableDefs(i).Name, 1) = "1" Then
    dbs.TableDefs.Delete dbs.TableDefs(i).Name
    End If
    Next i

    Set dbs = Nothing
    End Function

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: delete records with ending 1? (Access 2000)

    Do you have to delete any relationships as well?

    For deleting just tables replace (in your existing function)

    If Left(dbs.TableDefs(i).Name, 4) <> "MSys" Then


    with

    If ( Left(dbs.TableDefs(i).Name, 4) <> "MSys) and ("Right(dbs.TableDefs(i).Name, 1) = "1") Then


    and remove the lines that remove the relations. You probably don't need the first half of this line, but I left it in just to doubly sure that you don't delete something you actually want kept.
    Regards
    John



  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete records with ending 1? (Access 2000)

    Thank you for your reply.I have copied your code, named the function DeleteSuffixedTables and tried it,
    however it deletes all the tables,not just the tables with the endings1. Maybe i am wrong? For example, if i have 2 tables,
    products and products1, i want to delete only the table products1


    Function DeleteSuffixedTables()
    ' delete all records with ending 1 e.g. products1
    Dim dbs As DAO.Database
    Dim i As Integer
    Set dbs = CurrentDb

    ' Loop backwards through relations
    For i = dbs.Relations.Count - 1 To 0 Step -1
    dbs.Relations.Delete dbs.Relations(i).Name
    Next i

    ' Loop backwards through tabledefs
    For i = dbs.TableDefs.Count - 1 To 0 Step -1
    If Left(dbs.TableDefs(i).Name, 4) <> "MSys" Then
    dbs.TableDefs.Delete dbs.TableDefs(i).Name
    End If
    Next i

    Set dbs = Nothing
    End Function




    best regards

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

    Re: delete records with ending 1? (Access 2000)

    The code you post here is still the old code, not the new version I posted.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete records with ending 1? (Access 2000)

    It is true what you said. Thank you for the patience. It goes without saying that it functiuons properly now

Posting Permissions

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