[Resolved][Access 97] Deleting Linked tables

Hi,

I have access 97 db and i want to deleted the tables that are linked to another DB. the reason for this is i have the live DB and want to relink to a development dataset.

The below code works however it has errors. the reason for the error is that the index of the table def changes once i have deleted a table.

can anyone sugest how i can keep the index while deleting the linked tables

Code:

---

Public Sub delTableLinks()

    Dim db As Database
    Dim tdfNew As TableDef
    Dim i As Integer
    Dim fld As Field
    Dim j As Integer

   
Set db = currentdb
    j = db.TableDefs.Count
    For i = 1 To j
        Set tdfNew = db.TableDefs(i)
            If Len(tdfNew.Connect) > 1 Then
            db.TableDefs.Delete (tdfNew.Name)
        End If
    Next
           
End Sub

---

Thanks in advance

David

vb Code:

Public Sub delTableLinks()
 
    Dim db As Database
    Dim tdfNew As TableDef
    Dim i As Integer
    Dim fld As Field
    Dim j As Integer
 
    
Set db = currentdb
    j = db.TableDefs.Count
    For i = 1 To j
        Set tdfNew = db.TableDefs(i)
            If Len(tdfNew.Connect) > 1 Then
            db.TableDefs.Delete (tdfNew.Name)
            If db.TableDefs.Count > 0 Then 
                j=db.TableDefs.count
            Else
                Exit Sub
            End If
        End If
    Next
            
End Sub

hi Max,

thanks for the above code.

unfortunetly The same problem still exists, the reason for it is the TableDefs.Count is reduced each time a table is deleted.

I was thinking that I may need to create a new procedure to manage the indexes.


Thanks

David

This will probably work if you use a common trick for removing items from an array (like the list of a ListBox, etc), which is to loop downwards instead of upwards, eg:

Code:

---

    For i = j To 1 Step -1

---

Hello,

Si that worked a treat, thanks

Public Sub delTableLinks()
'Created 20/11/07, This procedure is used to delete all linked tables from an access 97 DB
'thanks to MaximilianMayrhofer and Si_The_Geek (VBforums.com) for their assistance in ironing out the errors.

Code:

---

Public Sub delTableLinksx()
'Created 20/11/07, This procedure is used to delete all linked tables from an access 97 DB
'thanks to MaximilianMayrhofer and Si_The_Geek (VBforums.com) for their assistance in ironing out the errors.

Dim db As Database
Dim tdfNew As TableDef
Dim i As Integer
Dim j As Integer

Set db = currentdb

j = db.TableDefs.Count - 1

 For i = j To 1 Step -1
    Set tdfNew = db.TableDefs(i)
    If Len(tdfNew.Connect) > 1 Then
        db.TableDefs.Delete (tdfNew.Name)
        If db.TableDefs.Count > 0 Then
            j = db.TableDefs.Count
        Else
            Exit Sub
        End If
    End If
Next
End Sub

---

cheers

David