Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete relationships (Access 2000)

    My function for deleting tables is not working, since i receive the mesage that the tables participate in one or two relationships.
    Cann i add on some code to delete these relationships,in order to delete all the tables after that.My current function is the following
    Function DeleteAll()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim frm As Document
    Dim rpt As Document
    Set db = CurrentDb
    For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
    DoCmd.DeleteObject acTable, tdf.Name
    End If
    Next
    End Function

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

    Re: delete relationships (Access 2000)

    You should go at this a bit differently. When you delete a TableDef, you change the TableDefs collection, so you will probably miss a few. You should step backwards through the collection. Same for the relationships: step backwards.

    Function DeleteAll()
    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

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

    Re: delete relationships (Access 2000)

    When I tried the code with For Each tdf In db.TableDefs, I had to run it several times before all tables had been deleted. When I used For i = db.TableDefs.Count -1 To 0 Step -1, all tables were deleted in one go.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete relationships (Access 2000)

    Thank you very much indeed for your reply.I have tried your suggestion but i must have missed something since i get the Run Time error 91 namely " "Object Variable or With block variable not set ". Could you check up my function again to find where my error is ?
    Here is the function:
    Function DeleteAll()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    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(tdf.Name, 4) <> "MSys" Then
    dbs.TableDefs.Delete dbs.TableDefs(i).Name
    End If
    Next i

    Set dbs = Nothing
    End Function


    Best Regards

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: delete relationships (Access 2000)

    Which instruction is giving error?
    Set a breakpoint and step thru one instruction at a time till you come to it.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete relationships (Access 2000)

    I feel obliged to let you know that the code suggested by you works now!! I did not cooy your suggestion in the right way
    but i have copied again and it is excellent !!!!!!!!!!!


    Best regards

Posting Permissions

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