A counting problem

[John Sh]

I have a table count of about 90. Set td = oDB.TableDefs td.count.
When I relink my tables from the development environment, an external hard drive, to the work environment, a university server, and vice versa, I get a file count of some 320.
How can I get the total number of tables to be relinked? I need this number to populate a progress gauge

Note. nRed, nGrn. nCng are private integer variables.

Public Sub re_Link()
    Dim T       As TableDef
    Dim td      As TableDefs
    Dim sSource As String
    Dim nNum2 As Integer
    Dim nNum1 As Integer
    nRed = 255
    nGrn = 0
    nCng = 0
    Me.btnGauge.Visible = True
    On Error Resume Next
    Set td = oDB.TableDefs
    nNum1 = IIf(oFSO.getDrive(TempVars!patha.Value).DriveType = 3, 320, 315)
    nNum2 = 1
    sSource = TempVars!src & "_be.accdb"
    For Each T In td
        If T.Connect <> ";DATABASE=" & sSource Then
            T.Connect = ";DATABASE=" & sSource
            T.RefreshLink
            doBar nNum1, nNum2
            nNum2 = nNum2 + 1
        End If
    Next
    Set T = Nothing
    Set td = Nothing
End Sub

Private Sub doBar(nNum1 As Integer, nNum2 As Integer)
    Dim nWidth As Long
    nWidth = Int(((nNum2 / nNum1) * 100))
    If nCng < nWidth Then
        nGrn = IIf(nGrn >= 255, 255, IIf(nRed = 255, nGrn + 6, nGrn))
        nRed = IIf(nRed <= 0, 0, IIf(nGrn = 255, nRed - 6, nRed))
        nCng = nWidth
        Me.btnGauge.BackColor = RGB(nRed, nGrn, 0)
    End If
    If nWidth > 10 Then
        Me.btnGauge.Left = Me.btnGauge.Left - 6
        Me.btnGauge.Width = nWidth * 40
    End If
    Me.btnGauge.Caption = nWidth & "%"
    DoEvents
End Sub

 

[ebs17]

SELECT Name, Database
FROM MSysObjects
WHERE Database > ""

This allows you to determine the linked tables and the associated backend. You could also process this list directly via Recordset.
You can also count easily, using a Count in the query or a RecordCount in the recordset.

 

[John Sh]

SELECT Name, Database
FROM MSysObjects
WHERE Database > ""

This allows you to determine the linked tables and the associated backend. You could also process this list directly via Recordset.
You can also count easily, using a Count in the query or a RecordCount in the recordset.

Thanks.
However td.count within the re_link sub actually returns the correct number of files.
I had checked it inside a form and that's where I got the lower number.
John