CODEWITHSUNDEEP
ms-accessvbams-access-2010
ionalchemist
ionalchemist

Reputation: 418

VBA code stops after loop with RefreshLink

The following code updates the links between my tables and selected backend. I have confirmed that the update works for all tables when I run the code. However, after successfully updating the links, the execution is interrupted and without an error message. The MsgBox (TableLinked) that follows does not execute.

When the initial If statement evaluates to False, MsgBox (TableLinked) is executed, which tells me there is a hang up in my loop.

Any ideas as to what is going on here?

If Not GetBackend = "" Then
    For i = 0 To Db.TableDefs.Count
        If Not Db.TableDefs(i).Connect = "" Then
            Db.TableDefs(i).Connect = ";DATABASE=" & GetBackend
            Db.TableDefs(i).RefreshLink
            TableLinked = True
        End If
    Next i
Else
    TableLinked = False
End If

MsgBox (TableLinked)

EDIT

Well, I'll assume my subscript was out of range (although it just fails silently if so). When I do this in line 2 For i = 0 To Db.TableDefs.Count - 1, my code lives on after the loop. Anyone else ever have this happen? My solution still doesn't answer my question really cause I still don't know with a certainty what's breaking the code.

Upvotes: 1

Views: 728

Answers (1)

HansUp
HansUp

Reputation: 97101

The member items of the TableDefs collection are numbered starting with zero. That means the index number of the last item is one less than the count of items. Frankly I don't understand why your code is not throwing error #3265, "Item not found in this collection" ... because the equivalent of TableDefs(TableDefs.Count) attempts to reference a TableDef which does not exist.

Revise your code like this ...

'For i = 0 To Db.TableDefs.Count
Dim lngLastItem As Long
lngLastItem = (Db.TableDefs.Count - 1)
For i = 0 To lngLastItem
    If Not Db.TableDefs(i).Connect = "" Then
    ' and the rest ...

Upvotes: 3

Related Questions