[Resolved][Access 97] Deleting Linked tables

[Davadvice]

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

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

[MaximilianMayrhofer]

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

[Davadvice]

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

[si_the_geek]

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:

    For i = j To 1 Step -1

[Davadvice]

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.

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