Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Deleting Attached Tables (XP)

    Thanks for the fast answer to my last post Hans. When I change from a jet backend to an SQL backend (or vice versa) is there a fast way to delete the existing table attachments without deleting front end tables? I've used to following that looks at the Foreign name to tell if it is an attached table - it seems to work but are there going to be any instances where the foreign field won't give me what I want?

    SQL1 = "SELECT Len([ForeignName]) AS LenFN, MSysObjects.Name " _
    & "FROM MSysObjects WHERE (((Len([ForeignName]))>0));"

    Set rstAttachedTables = CurrentDb.OpenRecordset(SQL1, dbOpenSnapshot)

    Do Until rstAttachedTables.EOF

    strTableName = rstAttachedTables!Name
    CurrentDb().TableDefs.Delete strTableName
    rstAttachedTables.MoveNext
    Loop

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

    Re: Deleting Attached Tables (XP)

    The ForeignName property is probably dependable. but I would use the Connect property of the TableDef object:

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    If Len(tdf.Connect) > 0 Then
    dbs.TableDefs.Delete tdf.Name
    End If
    Next tdf

    Set tdf = Nothing
    Set dbs = Nothing

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting Attached Tables (XP)

    Your code works but requires 4 passes to delete all the tables, I have stepped through the code and all looks OK but each time I run it it leaves some undeleted. Very strange. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: Deleting Attached Tables (XP)

    Does this work better?

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim dbs As DAO.Database
    Dim i As Long

    Set dbs = CurrentDb
    For i = dbs.TableDefs.Count - 1 To 0 Step -1
    If Len(dbs.TableDefs(i).Connect) > 0 Then
    dbs.TableDefs.Delete dbs.TableDefs(i).Name
    End If
    Next i

    Set dbs = Nothing

    <img src=/w3timages/blueline.gif width=33% height=2>

    By looping backwards, you should avoid the problem, hopefully.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting Attached Tables (XP)

    That got it - thanks.

Posting Permissions

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