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

    Delete all tables but one (Access 2000)

    I have the folowing funciton for deleting all tables:
    Public Function DeleteAllTables()
    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

    How could i modify it so as not to delete the table "KiaPrices" ?

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

    Re: Delete all tables but one (Access 2000)

    Change

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

    to

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

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

    Re: Delete all tables but one (Access 2000)

    Thank you very much indeed !

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete all tables but one (Access 2000)

    Should that not be:
    If Left(dbs.TableDefs(i).Name, 4) <> "MSys" Or dbs.TableDefs(i).Name <> "KiaPrices" Then
    Regards,
    Rudi

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

    Re: Delete all tables but one (Access 2000)

    I surely hope not! *Any* name satisfies either the first condition or the second one, so the combination with Or will always be True.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete all tables but one (Access 2000)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    That was a dull (or Null) moment!
    I forgot the MSys was the last 4 characters of a table. I was thinking about a table called "MSys" OR a table called "Kia..." In this event AND would not find anything!

    Sorry...
    Regards,
    Rudi

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

    Re: Delete all tables but one (Access 2000)

    > MSys was the last 4 characters of a table

    No! The code tests whether the first 4 characters are "MSys"!

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete all tables but one (Access 2000)

    If you plan on creating other tables not for deletion you could also just append those tables with, say "keep_<table name>" and edit the code accordingly. this abstracts the logic a little.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete all tables but one (Access 2000)

    <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22> ### yeh...what can I say! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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